# Backstock System Implementation Plan This document outlines the implementation plan for enhancing the backstock system. All implementations follow existing codebase patterns: Repository + Mapper + Controller architecture, SQL migrations with migration_log tracking, and Slim 2.6.2 route grouping. --- ## Table of Contents 1. [High Priority Features](#high-priority-features) - [1.1 Seasonal Intelligence](#11-seasonal-intelligence) - [1.2 Category Cleanup & Hierarchy](#12-category-cleanup--hierarchy) - [1.3 Location Granularity](#13-location-granularity) 2. [Medium Priority Features](#medium-priority-features) - [2.1 Reporting Dashboard](#21-reporting-dashboard) - [2.2 Better Bin Naming](#22-better-bin-naming) - [2.3 Mobile-First UI](#23-mobile-first-ui) - [2.4 Enhanced Action Types](#24-enhanced-action-types) - [2.5 Backstock Notes](#25-backstock-notes) 3. [File Structure Overview](#file-structure-overview) 4. [Database Migrations](#database-migrations) 5. [Testing Strategy](#testing-strategy) --- ## High Priority Features ### 1.1 Seasonal Events & Intelligence **Goal:** Create a seasonal event system where stores can configure events (Back to School, Black Friday, Tax Free Weekend, Easter, Christmas, Summer, Winter, etc.) with start dates, build-up periods, and category tags for bins. #### Concept ``` ┌─────────────────────────────────────────────────────────────────────────┐ │ SEASONAL EVENT TIMELINE │ ├─────────────────────────────────────────────────────────────────────────┤ │ │ │ Build-up Period Event Active Wind-down │ │ (Pull from storage) (On floor) (Store away) │ │ │ │ ◄──── 14 days ────► ◄──── Event Duration ────► ◄── 7 days ──► │ │ │ │ ┌─────────────────┐ ┌─────────────────────────┐ ┌─────────────┐ │ │ │ 📦 Pulling │ │ 🏪 On Floor │ │ 📦 Storing │ │ │ │ from storage │──│ Ready to sell │──│ back away │ │ │ └─────────────────┘ └─────────────────────────┘ └─────────────┘ │ │ ▲ ▲ │ │ │ │ │ │ Event Start Event End │ │ │ └─────────────────────────────────────────────────────────────────────────┘ ``` #### Database Schema ```sql -- Migration: 2025_01_backstock_seasonal_events.sql -- Event templates (global, in kiosk_buykiosk) -- Pre-populated with common retail events CREATE TABLE bsEventTemplates ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, -- 'Back to School', 'Black Friday' eventType ENUM('season', 'holiday', 'sale', 'custom') NOT NULL, description VARCHAR(255), defaultBuildUpDays INT DEFAULT 14, -- Default days to prepare defaultWindDownDays INT DEFAULT 7, -- Default days to store after suggestedMonth TINYINT, -- Typical month (1-12) suggestedDay TINYINT, -- Typical day of month icon VARCHAR(50), -- FontAwesome icon class color VARCHAR(10), -- Hex color for UI created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Pre-populate common events INSERT INTO bsEventTemplates (name, eventType, description, defaultBuildUpDays, defaultWindDownDays, suggestedMonth, suggestedDay, icon, color) VALUES ('Spring', 'season', 'Spring season merchandise', 21, 14, 3, 1, 'fa-seedling', '7cb342'), ('Summer', 'season', 'Summer season merchandise', 21, 14, 5, 15, 'fa-sun', 'ffb300'), ('Fall', 'season', 'Fall season merchandise', 21, 14, 9, 1, 'fa-leaf', 'e65100'), ('Winter', 'season', 'Winter season merchandise', 21, 14, 11, 1, 'fa-snowflake', '29b6f6'), ('Back to School', 'sale', 'School supplies, uniforms, backpacks', 21, 7, 7, 15, 'fa-school', '5c6bc0'), ('Easter', 'holiday', 'Easter dresses, spring formal', 14, 3, 4, 1, 'fa-egg', 'ab47bc'), ('Halloween', 'holiday', 'Costumes, fall decor', 21, 3, 10, 1, 'fa-ghost', 'ff7043'), ('Thanksgiving', 'holiday', 'Fall formal, family gathering attire', 14, 3, 11, 15, 'fa-drumstick-bite', 'a1887f'), ('Black Friday', 'sale', 'Major sale event preparation', 7, 3, 11, 25, 'fa-tags', '424242'), ('Christmas', 'holiday', 'Holiday attire, winter formal, gifts', 28, 7, 12, 1, 'fa-tree', 'c62828'), ('Tax Free Weekend', 'sale', 'State tax-free shopping event', 7, 1, 8, 1, 'fa-percent', '00897b'), ('Valentine\'s Day', 'holiday', 'Formal wear, red/pink items', 14, 3, 2, 1, 'fa-heart', 'e91e63'), ('St. Patrick\'s Day', 'holiday', 'Green items, Irish themes', 7, 1, 3, 10, 'fa-clover', '43a047'), ('Mother\'s Day', 'holiday', 'Gift items, spring formal', 14, 3, 5, 1, 'fa-flower', 'f06292'), ('Father\'s Day', 'holiday', 'Men\'s items, gift items', 14, 3, 6, 1, 'fa-user-tie', '1976d2'), ('4th of July', 'holiday', 'Red/white/blue, summer items', 14, 3, 7, 1, 'fa-flag-usa', '1565c0'), ('New Year', 'holiday', 'Formal wear, party attire', 14, 7, 12, 26, 'fa-glass-cheers', 'ffd54f'); -- Per-store event configuration (in each store database) CREATE TABLE bsEvents ( id INT AUTO_INCREMENT PRIMARY KEY, templateId INT, -- FK to bsEventTemplates (NULL for custom) name VARCHAR(100) NOT NULL, -- Event name (can override template) eventType ENUM('season', 'holiday', 'sale', 'custom') NOT NULL DEFAULT 'custom', year YEAR NOT NULL, -- Year this event applies to startDate DATE NOT NULL, -- When event starts (items on floor) endDate DATE NOT NULL, -- When event ends buildUpDays INT NOT NULL DEFAULT 14, -- Days before start to begin pulling windDownDays INT NOT NULL DEFAULT 7, -- Days after end to complete storing color VARCHAR(10), -- Override color icon VARCHAR(50), -- Override icon notes TEXT, isActive TINYINT DEFAULT 1, isRecurring TINYINT DEFAULT 1, -- Auto-create next year created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_dates (startDate, endDate), INDEX idx_year (year), INDEX idx_active (isActive) ); -- Link categories to events (the "tags" for bins) CREATE TABLE bsEvent_Categories ( id INT AUTO_INCREMENT PRIMARY KEY, eventId INT NOT NULL, categoryId INT NOT NULL, priority TINYINT DEFAULT 5, -- 1-10, higher = pull first notes VARCHAR(255), -- e.g., "Only winter coats, not light jackets" UNIQUE KEY uk_event_cat (eventId, categoryId), INDEX idx_event (eventId), INDEX idx_category (categoryId), FOREIGN KEY (eventId) REFERENCES bsEvents(id) ON DELETE CASCADE ); -- Event progress tracking CREATE TABLE bsEvent_Progress ( id INT AUTO_INCREMENT PRIMARY KEY, eventId INT NOT NULL, phase ENUM('upcoming', 'build_up', 'active', 'wind_down', 'completed') NOT NULL, binsPulled INT DEFAULT 0, binsOnFloor INT DEFAULT 0, binsStored INT DEFAULT 0, lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_event (eventId), INDEX idx_phase (phase) ); -- Alerts/notifications for events CREATE TABLE bsEvent_Alerts ( id INT AUTO_INCREMENT PRIMARY KEY, eventId INT NOT NULL, alertType ENUM( 'build_up_starting', -- Build-up period starting soon 'build_up_behind', -- Behind on pulling bins 'event_starting', -- Event starts soon 'event_started', -- Event has started 'wind_down_starting', -- Time to start storing 'wind_down_behind', -- Behind on storing bins 'event_ended' -- Event completed ) NOT NULL, message TEXT NOT NULL, binCount INT, acknowledged TINYINT DEFAULT 0, acknowledgedBy INT, -- employeeId acknowledgedAt TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_event (eventId), INDEX idx_unacknowledged (acknowledged, created_at) ); ``` #### Visual: Event Management Page ``` ┌──────────────────────────────────────────────────────────────────────────────┐ │ 📅 Seasonal Events [+ New Event]│ ├──────────────────────────────────────────────────────────────────────────────┤ │ │ │ ┌─ UPCOMING ──────────────────────────────────────────────────────────────┐ │ │ │ │ │ │ │ 🎃 Halloween Oct 1 - Oct 31, 2025 │ │ │ │ Build-up: Sep 10 21 days to prepare │ │ │ │ Categories: Halloween Clothing, Costumes, Fall Decor │ │ │ │ Bins to pull: 15 [View Bins] [Edit Event] │ │ │ │ ████████░░░░░░░░░░░░ 40% ready │ │ │ │ │ │ │ │ 🎄 Christmas Dec 1 - Dec 25, 2025 │ │ │ │ Build-up: Nov 3 28 days to prepare │ │ │ │ Categories: Christmas PJs, Christmas Clothing, Winter Formal │ │ │ │ Bins to pull: 42 [View Bins] [Edit Event] │ │ │ │ ░░░░░░░░░░░░░░░░░░░░ Not started │ │ │ │ │ │ │ └──────────────────────────────────────────────────────────────────────────┘ │ │ │ │ ┌─ ACTIVE NOW ────────────────────────────────────────────────────────────┐ │ │ │ │ │ │ │ ☀️ Summer May 15 - Sep 1, 2025 │ │ │ │ Categories: Swimwear, Sandals, Summer Clothing, Tanks │ │ │ │ On floor: 135 bins [View Bins] [Edit Event] │ │ │ │ ████████████████████ 100% on floor │ │ │ │ │ │ │ └──────────────────────────────────────────────────────────────────────────┘ │ │ │ │ ┌─ COMPLETED THIS YEAR ───────────────────────────────────────────────────┐ │ │ │ │ │ │ │ 🐰 Easter (Apr 1-20) ✓ 🇺🇸 4th of July (Jun 20 - Jul 5) ✓ │ │ │ │ 🎒 Back to School (Jul 15 - Sep 1) ✓ │ │ │ │ │ │ │ └──────────────────────────────────────────────────────────────────────────┘ │ │ │ │ [📊 Event Calendar View] [📋 Copy Events to Next Year] │ │ │ └──────────────────────────────────────────────────────────────────────────────┘ ``` #### Visual: Add/Edit Event Modal ``` ┌──────────────────────────────────────────────────────────────────────────────┐ │ ✏️ Edit Event: Halloween [X] │ ├──────────────────────────────────────────────────────────────────────────────┤ │ │ │ Template: [Halloween (Holiday) ▼] □ Create from scratch │ │ │ │ ┌─ BASIC INFO ────────────────────────────────────────────────────────────┐ │ │ │ Event Name: [Halloween ] │ │ │ │ Event Type: ○ Season ● Holiday ○ Sale ○ Custom │ │ │ │ Color: [🎨 #ff7043] Icon: [🎃 fa-ghost ▼] │ │ │ └──────────────────────────────────────────────────────────────────────────┘ │ │ │ │ ┌─ DATES ─────────────────────────────────────────────────────────────────┐ │ │ │ │ │ │ │ Event Starts: [Oct 1, 2025 📅] Event Ends: [Oct 31, 2025 📅] │ │ │ │ │ │ │ │ Build-up Period: [21] days before (Start pulling: Sep 10) │ │ │ │ Wind-down Period: [3 ] days after (Finish storing: Nov 3) │ │ │ │ │ │ │ │ □ Recurring event (auto-create for next year) │ │ │ │ │ │ │ └──────────────────────────────────────────────────────────────────────────┘ │ │ │ │ ┌─ CATEGORY TAGS ─────────────────────────────────────────────────────────┐ │ │ │ │ │ │ │ Assign categories to this event. Bins with these categories will │ │ │ │ appear in the "pull" list during build-up. │ │ │ │ │ │ │ │ Selected Categories: │ │ │ │ ┌─────────────────────┬──────────┬───────────────────────┐ │ │ │ │ │ Category │ Priority │ Notes │ │ │ │ │ ├─────────────────────┼──────────┼───────────────────────┤ │ │ │ │ │ Halloween Clothing │ [High ▼] │ [ ] │ [🗑️] │ │ │ │ │ Halloween │ [High ▼] │ [ ] │ [🗑️] │ │ │ │ │ Costumes │ [Med ▼] │ [Kids sizes only ] │ [🗑️] │ │ │ │ └─────────────────────┴──────────┴───────────────────────┘ │ │ │ │ │ │ │ │ [+ Add Category] │ │ │ │ │ │ │ │ Available: [Search categories... ] │ │ │ │ ┌─────────────────────────────────────────┐ │ │ │ │ │ □ Fall □ Autumn │ │ │ │ │ │ □ Orange/Black □ Decor │ │ │ │ │ └─────────────────────────────────────────┘ │ │ │ │ │ │ │ └──────────────────────────────────────────────────────────────────────────┘ │ │ │ │ Notes: [ ] │ │ │ │ [Cancel] [Save Event] │ └──────────────────────────────────────────────────────────────────────────────┘ ``` #### File Structure ``` userfrosting/ ├── models/Class/Backstock/ │ ├── Season/ │ │ ├── SeasonConfig.php # Entity │ │ ├── SeasonConfigRepository.php # Database access │ │ ├── SeasonConfigMapper.php # JSON mapping │ │ ├── CategorySeason.php # Junction entity │ │ ├── CategorySeasonRepository.php │ │ ├── SeasonAlert.php # Alert entity │ │ ├── SeasonAlertRepository.php │ │ └── SeasonService.php # Business logic │ └── ... ├── controllers/Backstock/ │ ├── BackstockController.php # Existing │ └── SeasonController.php # New ├── routes/groups/ │ └── backstock.php # Add season routes └── templates/themes/default/backstock/ ├── seasons/ │ ├── config.html # Season configuration page │ ├── dashboard-widget.html # Dashboard alerts widget │ └── modals/ │ ├── addSeason.html │ ├── editSeason.html │ └── linkCategories.html └── js/ └── seasons.js ``` #### SeasonService.php (Core Business Logic) ```php store = $store; $this->seasonRepo = new SeasonConfigRepository($store); $this->catSeasonRepo = new CategorySeasonRepository($store); $this->alertRepo = new SeasonAlertRepository($store); $this->log = new \KLogger($_ENV['LOG_DIR'] . "backstock_seasons.log", \KLogger::DEBUG); } /** * Get bins that should be pulled from storage for upcoming season */ public function getBinsToPull(int $daysAhead = 14): array { $upcomingSeasons = $this->seasonRepo->findByPullDateRange( date('Y-m-d'), date('Y-m-d', strtotime("+{$daysAhead} days")) ); $bins = []; foreach ($upcomingSeasons as $season) { $categoryIds = $this->catSeasonRepo->getCategoryIdsBySeason($season->getId()); $seasonBins = $this->getBinsByCategories($categoryIds, 'off-site'); foreach ($seasonBins as $bin) { $bin->seasonName = $season->getSeasonName(); $bin->pullByDate = $season->getPullEndDate(); $bin->priority = $this->catSeasonRepo->getPriority($bin->mainCategory, $season->getId()); $bins[] = $bin; } } // Sort by priority (highest first), then by age (oldest first) usort($bins, function($a, $b) { if ($a->priority !== $b->priority) { return $b->priority - $a->priority; } return $b->age - $a->age; }); return $bins; } /** * Get bins that should be stored after season ends */ public function getBinsToStore(int $daysAhead = 14): array { $endingSeasons = $this->seasonRepo->findByStoreDateRange( date('Y-m-d'), date('Y-m-d', strtotime("+{$daysAhead} days")) ); $bins = []; foreach ($endingSeasons as $season) { $categoryIds = $this->catSeasonRepo->getCategoryIdsBySeason($season->getId()); $seasonBins = $this->getBinsByCategories($categoryIds, 'on-site'); foreach ($seasonBins as $bin) { $bin->seasonName = $season->getSeasonName(); $bin->storeByDate = $season->getStoreEndDate(); $bins[] = $bin; } } return $bins; } /** * Generate alerts for dashboard */ public function generateAlerts(): array { $alerts = []; // Pull alerts $binsToPull = $this->getBinsToPull(14); if (count($binsToPull) > 0) { $grouped = $this->groupBinsBySeason($binsToPull); foreach ($grouped as $seasonName => $seasonBins) { $daysUntilPull = $this->getDaysUntil($seasonBins[0]->pullByDate); $alerts[] = [ 'type' => $daysUntilPull <= 0 ? 'pull_now' : 'pull_upcoming', 'seasonName' => $seasonName, 'binCount' => count($seasonBins), 'daysUntil' => $daysUntilPull, 'message' => $this->formatPullMessage($seasonName, count($seasonBins), $daysUntilPull) ]; } } // Store alerts $binsToStore = $this->getBinsToStore(14); if (count($binsToStore) > 0) { $grouped = $this->groupBinsBySeason($binsToStore); foreach ($grouped as $seasonName => $seasonBins) { $daysUntilStore = $this->getDaysUntil($seasonBins[0]->storeByDate); $alerts[] = [ 'type' => $daysUntilStore <= 0 ? 'store_now' : 'store_upcoming', 'seasonName' => $seasonName, 'binCount' => count($seasonBins), 'daysUntil' => $daysUntilStore, 'message' => $this->formatStoreMessage($seasonName, count($seasonBins), $daysUntilStore) ]; } } // Overdue alerts (items that should have been moved 7+ days ago) $overduePull = $this->getBinsToPull(-7); $overdueStore = $this->getBinsToStore(-7); // ... add overdue alerts return $alerts; } /** * Get seasonal readiness summary */ public function getSeasonalReadiness(): array { $seasons = $this->seasonRepo->findActive(); $readiness = []; foreach ($seasons as $season) { $categoryIds = $this->catSeasonRepo->getCategoryIdsBySeason($season->getId()); $onSiteBins = $this->getBinsByCategories($categoryIds, 'on-site'); $offSiteBins = $this->getBinsByCategories($categoryIds, 'off-site'); $total = count($onSiteBins) + count($offSiteBins); $readiness[] = [ 'seasonName' => $season->getSeasonName(), 'pullStartDate' => $season->getPullStartDate(), 'storeStartDate' => $season->getStoreStartDate(), 'totalBins' => $total, 'onSiteCount' => count($onSiteBins), 'offSiteCount' => count($offSiteBins), 'readinessPercent' => $total > 0 ? round((count($onSiteBins) / $total) * 100) : 0, 'status' => $this->calculateSeasonStatus($season, count($onSiteBins), count($offSiteBins)) ]; } return $readiness; } private function calculateSeasonStatus(SeasonConfig $season, int $onSite, int $offSite): string { $now = new \DateTime(); $pullStart = new \DateTime($season->getPullStartDate()); $pullEnd = new \DateTime($season->getPullEndDate()); $storeStart = new \DateTime($season->getStoreStartDate()); if ($now < $pullStart) { return 'pre-season'; } elseif ($now >= $pullStart && $now <= $pullEnd) { $percent = $onSite / max(1, $onSite + $offSite); if ($percent >= 0.9) return 'ready'; if ($percent >= 0.5) return 'in-progress'; return 'behind'; } elseif ($now > $pullEnd && $now < $storeStart) { return 'in-season'; } else { return 'post-season'; } } // ... helper methods } ``` #### API Routes (add to backstock.php) ```php // Season configuration $app->group('/seasons', function() use ($app) { // Get all season configs $app->get('/', function($typeNum) use ($app) { $store = getStoreOrFail($app, $typeNum); $service = new SeasonService($store); echo json_encode($service->getAllSeasons()); }); // Get seasonal alerts for dashboard $app->get('/alerts', function($typeNum) use ($app) { $store = getStoreOrFail($app, $typeNum); $service = new SeasonService($store); echo json_encode($service->generateAlerts()); }); // Get bins to pull $app->get('/pull', function($typeNum) use ($app) { $store = getStoreOrFail($app, $typeNum); $daysAhead = $app->request->get('days') ?? 14; $service = new SeasonService($store); echo json_encode($service->getBinsToPull((int)$daysAhead)); }); // Get bins to store $app->get('/store', function($typeNum) use ($app) { $store = getStoreOrFail($app, $typeNum); $daysAhead = $app->request->get('days') ?? 14; $service = new SeasonService($store); echo json_encode($service->getBinsToStore((int)$daysAhead)); }); // Get seasonal readiness $app->get('/readiness', function($typeNum) use ($app) { $store = getStoreOrFail($app, $typeNum); $service = new SeasonService($store); echo json_encode($service->getSeasonalReadiness()); }); // Create season config $app->post('/', function($typeNum) use ($app) { $store = getStoreOrFail($app, $typeNum); $data = json_decode($app->request->getBody(), true); $mapper = new SeasonConfigMapper(); $config = $mapper->mapFromArray($data); $repo = new SeasonConfigRepository($store); $id = $repo->save($config); echo json_encode(['success' => true, 'id' => $id]); }); // Update season config $app->post('/:seasonId', function($typeNum, $seasonId) use ($app) { $store = getStoreOrFail($app, $typeNum); $data = json_decode($app->request->getBody(), true); $repo = new SeasonConfigRepository($store); $config = $repo->findById((int)$seasonId); if (!$config) { $app->halt(404, json_encode(['error' => 'Season not found'])); } $mapper = new SeasonConfigMapper(); $config = $mapper->updateFromArray($config, $data); $repo->save($config); echo json_encode(['success' => true]); }); // Link categories to season $app->post('/:seasonId/categories', function($typeNum, $seasonId) use ($app) { $store = getStoreOrFail($app, $typeNum); $data = json_decode($app->request->getBody(), true); $repo = new CategorySeasonRepository($store); $repo->syncCategories((int)$seasonId, $data['categoryIds'], $data['priorities'] ?? []); echo json_encode(['success' => true]); }); // Acknowledge alert $app->post('/alerts/:alertId/acknowledge', function($typeNum, $alertId) use ($app) { $store = getStoreOrFail($app, $typeNum); $employeeId = $app->request->post('employeeId'); $repo = new SeasonAlertRepository($store); $repo->acknowledge((int)$alertId, (int)$employeeId); echo json_encode(['success' => true]); }); }); ``` --- ### 1.2 Category Cleanup & Hierarchy **Goal:** Clean up duplicate categories, add parent/child hierarchy, improve data quality. #### Database Schema ```sql -- Migration: 2025_02_backstock_category_hierarchy.sql -- Add hierarchy support to existing categories table ALTER TABLE bsCategories ADD COLUMN parentId INT NULL AFTER id, ADD COLUMN sortOrder INT DEFAULT 0, ADD COLUMN isActive TINYINT DEFAULT 1, ADD COLUMN categoryType ENUM('gender', 'size', 'season', 'type', 'holiday', 'custom') DEFAULT 'custom', ADD INDEX idx_parent (parentId), ADD INDEX idx_type (categoryType); -- Category merge log (for audit trail) CREATE TABLE bsCategoryMergeLog ( id INT AUTO_INCREMENT PRIMARY KEY, sourceId INT NOT NULL, sourceName VARCHAR(150) NOT NULL, targetId INT NOT NULL, targetName VARCHAR(150) NOT NULL, binsAffected INT NOT NULL, mergedBy INT NOT NULL, -- employeeId mergedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Category templates (suggested categories per store type) -- In kiosk_buykiosk (global) CREATE TABLE bsCategoryTemplates ( id INT AUTO_INCREMENT PRIMARY KEY, storeType TINYINT NOT NULL, -- 1=Plato's, 2=Once Upon, etc. name VARCHAR(150) NOT NULL, parentTemplateName VARCHAR(150), categoryType ENUM('gender', 'size', 'season', 'type', 'holiday', 'custom'), suggestedColor VARCHAR(10), sortOrder INT DEFAULT 0, INDEX idx_store_type (storeType) ); ``` #### File Structure ``` userfrosting/ ├── models/Class/Backstock/ │ ├── Category/ │ │ ├── Category.php # Enhanced entity │ │ ├── CategoryRepository.php # Enhanced repository │ │ ├── CategoryService.php # Business logic │ │ ├── CategoryMerger.php # Merge logic │ │ └── CategoryHierarchy.php # Tree operations │ └── ... ├── controllers/Backstock/ │ └── CategoryController.php # New └── templates/themes/default/backstock/ ├── categories/ │ ├── manage.html # Full category management page │ ├── hierarchy.html # Tree view │ └── cleanup.html # Duplicate detection & merge └── js/ └── categoryManager.js ``` #### CategoryService.php ```php store = $store; $this->repo = new CategoryRepository($store); } /** * Find duplicate/similar categories */ public function findDuplicates(): array { $categories = $this->repo->findAll(); $duplicates = []; foreach ($categories as $i => $cat1) { $normalized1 = $this->normalizeForComparison($cat1->getName()); foreach ($categories as $j => $cat2) { if ($i >= $j) continue; $normalized2 = $this->normalizeForComparison($cat2->getName()); $similarity = $this->calculateSimilarity($normalized1, $normalized2); if ($similarity >= 0.85) { $duplicates[] = [ 'category1' => $cat1->toArray(), 'category2' => $cat2->toArray(), 'similarity' => $similarity, 'reason' => $this->explainSimilarity($cat1->getName(), $cat2->getName()) ]; } } } return $duplicates; } /** * Normalize category name for comparison */ private function normalizeForComparison(string $name): string { $name = strtolower(trim($name)); $name = preg_replace('/\s+/', ' ', $name); // Multiple spaces to single $name = preg_replace('/[^a-z0-9 ]/', '', $name); // Remove special chars return $name; } /** * Calculate similarity between two strings */ private function calculateSimilarity(string $s1, string $s2): float { if ($s1 === $s2) return 1.0; similar_text($s1, $s2, $percent); return $percent / 100; } /** * Get categories with usage statistics */ public function getCategoriesWithStats(): array { return $this->repo->findAllWithStats(); } /** * Get unused categories (0 bins) */ public function getUnusedCategories(): array { return $this->repo->findUnused(); } /** * Get category hierarchy as tree */ public function getHierarchy(): array { $categories = $this->repo->findAll(); return $this->buildTree($categories); } private function buildTree(array $categories, ?int $parentId = null): array { $branch = []; foreach ($categories as $category) { if ($category->getParentId() === $parentId) { $children = $this->buildTree($categories, $category->getId()); $node = $category->toArray(); if ($children) { $node['children'] = $children; } $branch[] = $node; } } usort($branch, fn($a, $b) => $a['sortOrder'] - $b['sortOrder']); return $branch; } /** * Suggest parent category based on name patterns */ public function suggestParent(string $categoryName): ?array { $patterns = [ // Size patterns -> suggest Gender parent '/^(\d+[T]?)\s*(Boy|Girl)/i' => 'gender', '/^(0-3|3-6|6-9|12|18|24)\s*Month\s*(Boy|Girl)/i' => 'gender', // Season patterns '/Summer|Swim|Sandal/i' => 'Summer', '/Winter|Snow|Coat/i' => 'Winter', '/Easter|Spring/i' => 'Spring', '/Halloween|Fall/i' => 'Fall', '/Christmas/i' => 'Christmas', ]; foreach ($patterns as $pattern => $suggestion) { if (preg_match($pattern, $categoryName, $matches)) { if ($suggestion === 'gender' && isset($matches[2])) { return $this->repo->findByName($matches[2]); } return $this->repo->findByName($suggestion); } } return null; } /** * Auto-fix common data quality issues */ public function autoFixDataQuality(): array { $fixes = []; $categories = $this->repo->findAll(); foreach ($categories as $category) { $originalName = $category->getName(); $fixedName = trim($originalName); // Fix trailing/leading whitespace if ($fixedName !== $originalName) { $category->setName($fixedName); $this->repo->save($category); $fixes[] = "Trimmed whitespace: '{$originalName}' -> '{$fixedName}'"; } // Fix color format (remove # if present) $color = $category->getColor(); if (strpos($color, '#') === 0) { $fixedColor = substr($color, 1); $category->setColor($fixedColor); $this->repo->save($category); $fixes[] = "Fixed color format: '{$color}' -> '{$fixedColor}'"; } } return $fixes; } } ``` #### CategoryMerger.php ```php store = $store; $this->repo = new CategoryRepository($store); $this->db = dbConnectByName($store->getDbName()); } /** * Merge source category into target category * - Updates all bins with source category to target * - Updates all sub-category references * - Logs the merge * - Optionally deletes source category */ public function merge(int $sourceId, int $targetId, int $employeeId, bool $deleteSource = true): array { $source = $this->repo->findById($sourceId); $target = $this->repo->findById($targetId); if (!$source || !$target) { throw new \InvalidArgumentException('Source or target category not found'); } if ($sourceId === $targetId) { throw new \InvalidArgumentException('Cannot merge category into itself'); } $this->db->beginTransaction(); try { // Update main category references $stmt = $this->db->prepare(" UPDATE bsBins SET mainCategory = :targetId WHERE mainCategory = :sourceId "); $stmt->execute(['targetId' => $targetId, 'sourceId' => $sourceId]); $binsUpdated = $stmt->rowCount(); // Update sub-category references in junction table $stmt = $this->db->prepare(" UPDATE bsBin_Cat SET catID = :targetId WHERE catID = :sourceId AND binID NOT IN ( SELECT binID FROM ( SELECT binID FROM bsBin_Cat WHERE catID = :targetId2 ) AS existing ) "); $stmt->execute([ 'targetId' => $targetId, 'sourceId' => $sourceId, 'targetId2' => $targetId ]); // Remove duplicate sub-category entries $stmt = $this->db->prepare(" DELETE FROM bsBin_Cat WHERE catID = :sourceId "); $stmt->execute(['sourceId' => $sourceId]); // Update season links $stmt = $this->db->prepare(" UPDATE bsCategory_Season SET categoryId = :targetId WHERE categoryId = :sourceId AND seasonConfigId NOT IN ( SELECT seasonConfigId FROM ( SELECT seasonConfigId FROM bsCategory_Season WHERE categoryId = :targetId2 ) AS existing ) "); $stmt->execute([ 'targetId' => $targetId, 'sourceId' => $sourceId, 'targetId2' => $targetId ]); // Log the merge $stmt = $this->db->prepare(" INSERT INTO bsCategoryMergeLog (sourceId, sourceName, targetId, targetName, binsAffected, mergedBy) VALUES (:sourceId, :sourceName, :targetId, :targetName, :binsAffected, :mergedBy) "); $stmt->execute([ 'sourceId' => $sourceId, 'sourceName' => $source->getName(), 'targetId' => $targetId, 'targetName' => $target->getName(), 'binsAffected' => $binsUpdated, 'mergedBy' => $employeeId ]); // Delete source category if requested if ($deleteSource) { $stmt = $this->db->prepare("DELETE FROM bsCategories WHERE id = :id"); $stmt->execute(['id' => $sourceId]); } $this->db->commit(); return [ 'success' => true, 'sourceName' => $source->getName(), 'targetName' => $target->getName(), 'binsUpdated' => $binsUpdated, 'sourceDeleted' => $deleteSource ]; } catch (\Exception $e) { $this->db->rollBack(); throw $e; } } /** * Bulk merge multiple categories into one */ public function bulkMerge(array $sourceIds, int $targetId, int $employeeId): array { $results = []; foreach ($sourceIds as $sourceId) { if ($sourceId !== $targetId) { $results[] = $this->merge($sourceId, $targetId, $employeeId); } } return $results; } } ``` --- ### 1.3 Location Granularity **Goal:** Support hierarchical locations (Building > Room > Aisle > Shelf) for easier bin finding. #### Database Schema ```sql -- Migration: 2025_03_backstock_location_hierarchy.sql -- Enhance existing locations table ALTER TABLE bsLocations ADD COLUMN parentId INT NULL AFTER id, ADD COLUMN locationType ENUM('building', 'room', 'aisle', 'shelf', 'zone', 'custom') DEFAULT 'custom', ADD COLUMN code VARCHAR(20), -- Short code for labels (e.g., "A-1-3") ADD COLUMN capacity INT, -- Max bins for this location ADD COLUMN sortOrder INT DEFAULT 0, ADD COLUMN description TEXT, ADD COLUMN qrCode VARCHAR(255), -- QR code data ADD INDEX idx_parent (parentId), ADD INDEX idx_code (code); -- Location usage tracking CREATE TABLE bsLocationHistory ( id INT AUTO_INCREMENT PRIMARY KEY, locationId INT NOT NULL, binId INT NOT NULL, action ENUM('moved_in', 'moved_out') NOT NULL, employeeId INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_location (locationId), INDEX idx_bin (binId), INDEX idx_date (created_at) ); ``` #### File Structure ``` userfrosting/ ├── models/Class/Backstock/ │ ├── Location/ │ │ ├── Location.php # Enhanced entity │ │ ├── LocationRepository.php # Enhanced repository │ │ ├── LocationService.php # Business logic │ │ └── LocationHierarchy.php # Tree operations │ └── ... ├── controllers/Backstock/ │ └── LocationController.php # New └── templates/themes/default/backstock/ ├── locations/ │ ├── manage.html # Full location management │ ├── tree.html # Hierarchical view │ ├── map.html # Visual map view │ └── labels.html # Print QR labels └── js/ └── locationManager.js ``` #### LocationService.php ```php store = $store; $this->repo = new LocationRepository($store); } /** * Get location hierarchy as tree */ public function getHierarchy(): array { $locations = $this->repo->findAll(); return $this->buildTree($locations); } private function buildTree(array $locations, ?int $parentId = null): array { $branch = []; foreach ($locations as $location) { if ($location->getParentId() === $parentId) { $children = $this->buildTree($locations, $location->getId()); $node = $location->toArray(); $node['binCount'] = $this->repo->getBinCount($location->getId()); $node['path'] = $this->getLocationPath($location->getId()); if ($children) { $node['children'] = $children; $node['totalBinCount'] = $this->getTotalBinCount($node); } $branch[] = $node; } } usort($branch, fn($a, $b) => $a['sortOrder'] - $b['sortOrder']); return $branch; } /** * Get full path for a location (e.g., "Storage > Aisle A > Shelf 1") */ public function getLocationPath(int $locationId): string { $path = []; $location = $this->repo->findById($locationId); while ($location) { array_unshift($path, $location->getName()); $location = $location->getParentId() ? $this->repo->findById($location->getParentId()) : null; } return implode(' > ', $path); } /** * Get location code for labels (e.g., "STG-A-1") */ public function generateLocationCode(int $locationId): string { $parts = []; $location = $this->repo->findById($locationId); while ($location) { $code = $location->getCode() ?: $this->abbreviate($location->getName()); array_unshift($parts, $code); $location = $location->getParentId() ? $this->repo->findById($location->getParentId()) : null; } return implode('-', $parts); } private function abbreviate(string $name): string { // "Storage" -> "STG", "Aisle A" -> "A", "Shelf 1" -> "1" $name = trim($name); if (preg_match('/^(Aisle|Shelf|Row|Bin)\s+(.+)$/i', $name, $matches)) { return $matches[2]; } if (strlen($name) <= 3) { return strtoupper($name); } return strtoupper(substr($name, 0, 3)); } /** * Find optimal location for a bin based on category */ public function suggestLocation(int $categoryId): ?array { // Find where similar bins are stored $stmt = $this->repo->getDb()->prepare(" SELECT l.id, l.name, COUNT(b.id) as binCount, (SELECT COUNT(*) FROM bsBins WHERE location = l.id AND deleted = 0) as totalBins, l.capacity FROM bsLocations l JOIN bsBins b ON b.location = l.id AND b.deleted = 0 WHERE b.mainCategory = :categoryId GROUP BY l.id ORDER BY binCount DESC LIMIT 5 "); $stmt->execute(['categoryId' => $categoryId]); $suggestions = $stmt->fetchAll(\PDO::FETCH_ASSOC); if (empty($suggestions)) { return null; } // Return location with most bins of same category that has capacity foreach ($suggestions as $suggestion) { if (!$suggestion['capacity'] || $suggestion['totalBins'] < $suggestion['capacity']) { $suggestion['path'] = $this->getLocationPath($suggestion['id']); return $suggestion; } } return $suggestions[0]; } /** * Get locations with capacity info */ public function getLocationsWithCapacity(): array { return $this->repo->findAllWithCapacity(); } /** * Generate QR code data for location */ public function generateQRData(int $locationId): string { $location = $this->repo->findById($locationId); $code = $this->generateLocationCode($locationId); return json_encode([ 'type' => 'backstock_location', 'store' => $this->store->getTypeNum(), 'locationId' => $locationId, 'code' => $code, 'path' => $this->getLocationPath($locationId) ]); } /** * Move bin to new location with history tracking */ public function moveBin(int $binId, int $newLocationId, int $employeeId): bool { $db = $this->repo->getDb(); $db->beginTransaction(); try { // Get current location $stmt = $db->prepare("SELECT location FROM bsBins WHERE id = :binId"); $stmt->execute(['binId' => $binId]); $oldLocationId = $stmt->fetchColumn(); // Update bin location $stmt = $db->prepare("UPDATE bsBins SET location = :locationId WHERE id = :binId"); $stmt->execute(['locationId' => $newLocationId, 'binId' => $binId]); // Log move out from old location if ($oldLocationId) { $stmt = $db->prepare(" INSERT INTO bsLocationHistory (locationId, binId, action, employeeId) VALUES (:locationId, :binId, 'moved_out', :employeeId) "); $stmt->execute([ 'locationId' => $oldLocationId, 'binId' => $binId, 'employeeId' => $employeeId ]); } // Log move in to new location $stmt = $db->prepare(" INSERT INTO bsLocationHistory (locationId, binId, action, employeeId) VALUES (:locationId, :binId, 'moved_in', :employeeId) "); $stmt->execute([ 'locationId' => $newLocationId, 'binId' => $binId, 'employeeId' => $employeeId ]); $db->commit(); return true; } catch (\Exception $e) { $db->rollBack(); throw $e; } } } ``` --- ## Medium Priority Features ### 2.1 Reporting Dashboard **Goal:** Provide comprehensive visual reports including: - **Aging Backstock Report** - Stacked horizontal bar chart showing bin counts by category and age bucket (like ResaleAI) - **Summary Statistics** - Key metrics at a glance - **Category Distribution** - Bin counts and health by category - **Location Utilization** - Storage usage - **Activity Reports** - Historical activity tracking - **Seasonal Readiness** - Event preparation status #### Visual: Reports Dashboard ``` ┌──────────────────────────────────────────────────────────────────────────────┐ │ 📊 Backstock Reports │ ├──────────────────────────────────────────────────────────────────────────────┤ │ │ │ ┌─ SUMMARY ───────────────────────────────────────────────────────────────┐ │ │ │ │ │ │ │ 📦 752 ⏱️ 64 days ⚠️ 60 bins 📍 85% │ │ │ │ Total Bins Avg Age Stale (>180d) Off-site │ │ │ │ │ │ │ └──────────────────────────────────────────────────────────────────────────┘ │ │ │ │ ┌─ QUICK REPORTS ─────────────────────────────────────────────────────────┐ │ │ │ │ │ │ │ [📊 Aging by Category] [📈 Age Distribution] [🏷️ Category Summary] │ │ │ │ [📍 Location Usage] [📅 Activity History] [🎯 Seasonal Readiness] │ │ │ │ [⚠️ Stale Inventory] [👥 Employee Activity] [📋 Full Export] │ │ │ │ │ │ │ └──────────────────────────────────────────────────────────────────────────┘ │ │ │ └──────────────────────────────────────────────────────────────────────────────┘ ``` #### Visual: Aging Backstock Report (Like ResaleAI Screenshot) ``` ┌──────────────────────────────────────────────────────────────────────────────┐ │ ← Aging Backstock │ ├──────────────────────────────────────────────────────────────────────────────┤ │ │ │ Legend: ████ Aged 0-90 ████ Aged 91-180 ████ Aged 181-365 ████ 366+ │ │ │ │ Filter: [All Categories ▼] Location: [All ▼] Sort: [Total Bins ▼] │ │ │ │ ────────────────────────────────────────────────────────────────────────── │ │ │ │ Swimwear ████████████████████████████████████████░░░░░░░░ 76 │ │ Sandals ██████████████████████████░░░░░░░░░░░░░░░░░░░░░ 58 │ │ 10 Girl ████████████████████████░░░░░░░░░░░░░░░░░░░░░░░ 46 │ │ Easter █████████████████░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░ 31 │ │ 4T Girl ███████████████░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░ 29 │ │ Snowsuits ██████████████░░░░▓▓▓▓░░░░░░░░░░░░░░░░░░░░░░░░░ 27 │ │ 6 Girl ████████████░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░ 24 │ │ Boots ███████████░░▓▓▓▓▓▓░░░░░░░░░░░░░░░░░░░░░░░░░░░░ 20 │ │ 3T Girl ██████████░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░ 20 │ │ ST Patty Day ██████████░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░ 20 │ │ 7 Girl █████████░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░ 19 │ │ 2T Girl █████████░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░ 18 │ │ 0-3 Month Girl █████████░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░ 18 │ │ Winter Coats ████░░▓▓▓▓▓▓▓▓████░░░░░░░░░░░░░░░░░░░░░░░░░░░░ 16 │ │ Women's Denim ████████████████████████████████████▓▓▓▓▓▓▓▓██ 52 │ │ Women's Outerwear ██████████████████████████████▓▓▓▓▓▓▓▓░░░░░░░░ 38 │ │ │ │ ────────────────────────────────────────────────────────────────────────── │ │ │ │ Showing 20 of 45 categories with bins [Show All] [Export PDF] [Export CSV]│ │ │ └──────────────────────────────────────────────────────────────────────────────┘ ████ = Green (0-90 days) ▓▓▓▓ = Yellow (91-180) ░░░░ = Orange (181-365) ▒▒▒▒ = Red (366+) ``` #### File Structure ``` userfrosting/ ├── models/Class/Backstock/ │ └── Reports/ │ ├── ReportService.php # Main report orchestrator │ ├── AgingReport.php # Aging by category (stacked bars) │ ├── SummaryReport.php # Dashboard summary stats │ ├── CategoryReport.php # Category distribution │ ├── LocationReport.php # Location utilization │ ├── ActivityReport.php # Historical activity │ ├── SeasonalReport.php # Event readiness │ └── ExportService.php # PDF/CSV export ├── controllers/Backstock/ │ └── ReportController.php ├── routes/groups/ │ └── backstock.php # Add report routes └── templates/themes/default/backstock/ ├── reports/ │ ├── index.html # Reports dashboard │ ├── aging.html # Aging report page │ ├── summary.html # Summary stats │ ├── categories.html # Category report │ ├── locations.html # Location report │ ├── activity.html # Activity report │ ├── seasonal.html # Seasonal readiness │ └── partials/ │ ├── aging-chart.html # Horizontal stacked bar │ ├── summary-cards.html # KPI cards │ ├── category-table.html # Category data table │ └── filters.html # Report filters └── js/ └── reports.js # Chart.js integration ``` #### API Routes (add to backstock.php) ```php // Reports $app->group('/reports', function() use ($app) { // Dashboard summary $app->get('/summary', function($typeNum) use ($app) { $store = getStoreOrFail($app, $typeNum); $service = new ReportService($store); echo json_encode($service->getSummary()); }); // Aging by category (for stacked bar chart) $app->get('/aging', function($typeNum) use ($app) { $store = getStoreOrFail($app, $typeNum); $locationId = $app->request->get('location'); $sortBy = $app->request->get('sort') ?? 'total'; $report = new AgingReport($store); echo json_encode($report->getAgingByCategory($locationId, $sortBy)); }); // Age distribution (simple buckets) $app->get('/age-distribution', function($typeNum) use ($app) { $store = getStoreOrFail($app, $typeNum); $service = new ReportService($store); echo json_encode($service->getAgeDistribution()); }); // Category summary $app->get('/categories', function($typeNum) use ($app) { $store = getStoreOrFail($app, $typeNum); $report = new CategoryReport($store); echo json_encode($report->getCategorySummary()); }); // Location utilization $app->get('/locations', function($typeNum) use ($app) { $store = getStoreOrFail($app, $typeNum); $report = new LocationReport($store); echo json_encode($report->getLocationUtilization()); }); // Activity history $app->get('/activity', function($typeNum) use ($app) { $store = getStoreOrFail($app, $typeNum); $startDate = $app->request->get('start') ?? date('Y-m-d', strtotime('-30 days')); $endDate = $app->request->get('end') ?? date('Y-m-d'); $report = new ActivityReport($store); echo json_encode($report->getActivity($startDate, $endDate)); }); // Stale inventory $app->get('/stale', function($typeNum) use ($app) { $store = getStoreOrFail($app, $typeNum); $threshold = $app->request->get('days') ?? 180; $service = new ReportService($store); echo json_encode($service->getStaleInventory((int)$threshold)); }); // Seasonal readiness $app->get('/seasonal', function($typeNum) use ($app) { $store = getStoreOrFail($app, $typeNum); $eventService = new EventService($store); echo json_encode($eventService->getSeasonalReadiness()); }); // Export $app->get('/export/:format', function($typeNum, $format) use ($app) { $store = getStoreOrFail($app, $typeNum); $reportType = $app->request->get('report') ?? 'summary'; $export = new ExportService($store); if ($format === 'csv') { header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="backstock_' . $reportType . '.csv"'); echo $export->toCSV($reportType); } elseif ($format === 'pdf') { header('Content-Type: application/pdf'); echo $export->toPDF($reportType); } }); }); ``` #### AgingReport.php (Key Report Class) ```php 0, 'max' => 90, 'label' => '0-90', 'color' => '#4CAF50'], // Green ['min' => 91, 'max' => 180, 'label' => '91-180', 'color' => '#FFC107'], // Yellow/Amber ['min' => 181, 'max' => 365, 'label' => '181-365', 'color' => '#FF9800'], // Orange ['min' => 366, 'max' => 9999, 'label' => '366+', 'color' => '#F44336'], // Red ]; public function __construct(\Store $store) { $this->store = $store; $this->db = dbConnectByName($store->getDbName()); } /** * Get aging data by category for stacked horizontal bar chart * Returns data formatted for Chart.js */ public function getAgingByCategory(?int $locationId = null, string $sortBy = 'total'): array { $locationFilter = $locationId ? "AND b.location = :locationId" : ""; $sql = " SELECT c.id as categoryId, c.name as categoryName, c.color as categoryColor, SUM(CASE WHEN DATEDIFF(NOW(), b.ageDate) BETWEEN 0 AND 90 THEN 1 ELSE 0 END) as age_0_90, SUM(CASE WHEN DATEDIFF(NOW(), b.ageDate) BETWEEN 91 AND 180 THEN 1 ELSE 0 END) as age_91_180, SUM(CASE WHEN DATEDIFF(NOW(), b.ageDate) BETWEEN 181 AND 365 THEN 1 ELSE 0 END) as age_181_365, SUM(CASE WHEN DATEDIFF(NOW(), b.ageDate) > 365 THEN 1 ELSE 0 END) as age_366_plus, COUNT(b.id) as total, ROUND(AVG(DATEDIFF(NOW(), b.ageDate))) as avgAge FROM bsCategories c INNER JOIN bsBins b ON b.mainCategory = c.id AND b.deleted = 0 {$locationFilter} GROUP BY c.id, c.name, c.color HAVING total > 0 "; // Add sorting switch ($sortBy) { case 'name': $sql .= " ORDER BY c.name ASC"; break; case 'oldest': $sql .= " ORDER BY avgAge DESC"; break; case 'stale': $sql .= " ORDER BY (age_181_365 + age_366_plus) DESC"; break; case 'total': default: $sql .= " ORDER BY total DESC"; break; } $stmt = $this->db->prepare($sql); if ($locationId) { $stmt->bindValue(':locationId', $locationId, \PDO::PARAM_INT); } $stmt->execute(); $categories = $stmt->fetchAll(\PDO::FETCH_ASSOC); // Format for Chart.js horizontal stacked bar return [ 'labels' => array_column($categories, 'categoryName'), 'datasets' => [ [ 'label' => 'Aged 0-90', 'data' => array_column($categories, 'age_0_90'), 'backgroundColor' => self::AGE_BUCKETS[0]['color'], ], [ 'label' => 'Aged 91-180', 'data' => array_column($categories, 'age_91_180'), 'backgroundColor' => self::AGE_BUCKETS[1]['color'], ], [ 'label' => 'Aged 181-365', 'data' => array_column($categories, 'age_181_365'), 'backgroundColor' => self::AGE_BUCKETS[2]['color'], ], [ 'label' => 'Aged 366+', 'data' => array_column($categories, 'age_366_plus'), 'backgroundColor' => self::AGE_BUCKETS[3]['color'], ], ], 'meta' => [ 'totals' => array_column($categories, 'total'), 'avgAges' => array_column($categories, 'avgAge'), 'categoryIds' => array_column($categories, 'categoryId'), ], 'summary' => $this->getAgingSummary($locationId) ]; } /** * Get overall aging summary statistics */ public function getAgingSummary(?int $locationId = null): array { $locationFilter = $locationId ? "AND location = :locationId" : ""; $sql = " SELECT COUNT(*) as totalBins, ROUND(AVG(DATEDIFF(NOW(), ageDate))) as avgAge, MAX(DATEDIFF(NOW(), ageDate)) as maxAge, MIN(DATEDIFF(NOW(), ageDate)) as minAge, SUM(CASE WHEN DATEDIFF(NOW(), ageDate) BETWEEN 0 AND 90 THEN 1 ELSE 0 END) as fresh, SUM(CASE WHEN DATEDIFF(NOW(), ageDate) BETWEEN 91 AND 180 THEN 1 ELSE 0 END) as aging, SUM(CASE WHEN DATEDIFF(NOW(), ageDate) BETWEEN 181 AND 365 THEN 1 ELSE 0 END) as stale, SUM(CASE WHEN DATEDIFF(NOW(), ageDate) > 365 THEN 1 ELSE 0 END) as veryStale FROM bsBins WHERE deleted = 0 {$locationFilter} "; $stmt = $this->db->prepare($sql); if ($locationId) { $stmt->bindValue(':locationId', $locationId, \PDO::PARAM_INT); } $stmt->execute(); $summary = $stmt->fetch(\PDO::FETCH_ASSOC); // Calculate percentages $total = (int)$summary['totalBins']; if ($total > 0) { $summary['freshPercent'] = round(($summary['fresh'] / $total) * 100, 1); $summary['agingPercent'] = round(($summary['aging'] / $total) * 100, 1); $summary['stalePercent'] = round(($summary['stale'] / $total) * 100, 1); $summary['veryStalePercent'] = round(($summary['veryStale'] / $total) * 100, 1); } return $summary; } /** * Get aging data by location */ public function getAgingByLocation(): array { $stmt = $this->db->query(" SELECT l.id as locationId, l.name as locationName, l.onsite, SUM(CASE WHEN DATEDIFF(NOW(), b.ageDate) BETWEEN 0 AND 90 THEN 1 ELSE 0 END) as age_0_90, SUM(CASE WHEN DATEDIFF(NOW(), b.ageDate) BETWEEN 91 AND 180 THEN 1 ELSE 0 END) as age_91_180, SUM(CASE WHEN DATEDIFF(NOW(), b.ageDate) BETWEEN 181 AND 365 THEN 1 ELSE 0 END) as age_181_365, SUM(CASE WHEN DATEDIFF(NOW(), b.ageDate) > 365 THEN 1 ELSE 0 END) as age_366_plus, COUNT(b.id) as total, ROUND(AVG(DATEDIFF(NOW(), b.ageDate))) as avgAge FROM bsLocations l LEFT JOIN bsBins b ON b.location = l.id AND b.deleted = 0 GROUP BY l.id, l.name, l.onsite ORDER BY total DESC "); return $stmt->fetchAll(\PDO::FETCH_ASSOC); } /** * Get bins for a specific category with age details * Used when user clicks on a category bar */ public function getBinsByCategory(int $categoryId): array { $stmt = $this->db->prepare(" SELECT b.id, b.name, b.uuid, b.ageDate, DATEDIFF(NOW(), b.ageDate) as age, l.name as locationName, l.onsite, CASE WHEN DATEDIFF(NOW(), b.ageDate) <= 90 THEN 'fresh' WHEN DATEDIFF(NOW(), b.ageDate) <= 180 THEN 'aging' WHEN DATEDIFF(NOW(), b.ageDate) <= 365 THEN 'stale' ELSE 'very_stale' END as ageStatus FROM bsBins b LEFT JOIN bsLocations l ON l.id = b.location WHERE b.mainCategory = :categoryId AND b.deleted = 0 ORDER BY age DESC "); $stmt->execute(['categoryId' => $categoryId]); return $stmt->fetchAll(\PDO::FETCH_ASSOC); } } ``` #### ReportService.php (Main Orchestrator) ```php store = $store; $this->db = dbConnectByName($store->getDbName()); } /** * Dashboard summary - key metrics at a glance */ public function getSummary(): array { $stmt = $this->db->query(" SELECT COUNT(*) as totalBins, ROUND(AVG(DATEDIFF(NOW(), ageDate))) as avgAge, MAX(DATEDIFF(NOW(), ageDate)) as maxAge, SUM(CASE WHEN DATEDIFF(NOW(), ageDate) > 180 THEN 1 ELSE 0 END) as staleBins, SUM(CASE WHEN l.onsite = 0 THEN 1 ELSE 0 END) as offSiteBins, SUM(CASE WHEN l.onsite = 1 THEN 1 ELSE 0 END) as onSiteBins FROM bsBins b LEFT JOIN bsLocations l ON l.id = b.location WHERE b.deleted = 0 "); $summary = $stmt->fetch(\PDO::FETCH_ASSOC); // Category count $stmt = $this->db->query("SELECT COUNT(DISTINCT mainCategory) as categoryCount FROM bsBins WHERE deleted = 0"); $summary['categoryCount'] = $stmt->fetchColumn(); // Location count $stmt = $this->db->query("SELECT COUNT(*) as locationCount FROM bsLocations"); $summary['locationCount'] = $stmt->fetchColumn(); // Recent activity (last 7 days) $stmt = $this->db->query(" SELECT COUNT(*) as recentActions FROM bsActions WHERE timePerformed >= DATE_SUB(NOW(), INTERVAL 7 DAY) "); $summary['recentActions'] = $stmt->fetchColumn(); // Calculate health score (0-100) $total = (int)$summary['totalBins']; if ($total > 0) { $stalePercent = ($summary['staleBins'] / $total) * 100; $summary['healthScore'] = max(0, round(100 - $stalePercent)); $summary['offSitePercent'] = round(($summary['offSiteBins'] / $total) * 100, 1); } else { $summary['healthScore'] = 100; $summary['offSitePercent'] = 0; } return $summary; } /** * Age distribution (simple buckets for pie/doughnut chart) */ public function getAgeDistribution(): array { $stmt = $this->db->query(" SELECT CASE WHEN DATEDIFF(NOW(), ageDate) <= 30 THEN '0-30 days' WHEN DATEDIFF(NOW(), ageDate) <= 60 THEN '31-60 days' WHEN DATEDIFF(NOW(), ageDate) <= 90 THEN '61-90 days' WHEN DATEDIFF(NOW(), ageDate) <= 180 THEN '91-180 days' WHEN DATEDIFF(NOW(), ageDate) <= 365 THEN '181-365 days' ELSE '366+ days' END as ageBucket, COUNT(*) as binCount FROM bsBins WHERE deleted = 0 GROUP BY ageBucket ORDER BY MIN(DATEDIFF(NOW(), ageDate)) "); $data = $stmt->fetchAll(\PDO::FETCH_ASSOC); return [ 'labels' => array_column($data, 'ageBucket'), 'data' => array_map('intval', array_column($data, 'binCount')), 'colors' => ['#4CAF50', '#8BC34A', '#CDDC39', '#FFC107', '#FF9800', '#F44336'] ]; } /** * Stale inventory detail */ public function getStaleInventory(int $daysThreshold = 180): array { $stmt = $this->db->prepare(" SELECT b.id, b.name, b.uuid, b.ageDate, DATEDIFF(NOW(), b.ageDate) as age, c.name as categoryName, c.color as categoryColor, l.name as locationName, l.onsite FROM bsBins b LEFT JOIN bsCategories c ON c.id = b.mainCategory LEFT JOIN bsLocations l ON l.id = b.location WHERE b.deleted = 0 AND DATEDIFF(NOW(), b.ageDate) > :threshold ORDER BY age DESC "); $stmt->execute(['threshold' => $daysThreshold]); $bins = $stmt->fetchAll(\PDO::FETCH_ASSOC); // Group by category for summary $byCategory = []; foreach ($bins as $bin) { $cat = $bin['categoryName'] ?? 'Uncategorized'; if (!isset($byCategory[$cat])) { $byCategory[$cat] = ['count' => 0, 'totalAge' => 0]; } $byCategory[$cat]['count']++; $byCategory[$cat]['totalAge'] += $bin['age']; } // Calculate averages foreach ($byCategory as $cat => &$data) { $data['avgAge'] = round($data['totalAge'] / $data['count']); } return [ 'bins' => $bins, 'byCategory' => $byCategory, 'summary' => [ 'count' => count($bins), 'threshold' => $daysThreshold, 'oldestAge' => $bins[0]['age'] ?? 0, 'avgAge' => count($bins) > 0 ? round(array_sum(array_column($bins, 'age')) / count($bins)) : 0 ] ]; } /** * Category health report */ public function getCategoryHealth(): array { $stmt = $this->db->query(" SELECT c.id, c.name, c.color, COUNT(b.id) as binCount, ROUND(AVG(DATEDIFF(NOW(), b.ageDate))) as avgAge, MAX(DATEDIFF(NOW(), b.ageDate)) as maxAge, SUM(CASE WHEN DATEDIFF(NOW(), b.ageDate) > 180 THEN 1 ELSE 0 END) as staleBins, SUM(CASE WHEN l.onsite = 1 THEN 1 ELSE 0 END) as onSite, SUM(CASE WHEN l.onsite = 0 THEN 1 ELSE 0 END) as offSite FROM bsCategories c INNER JOIN bsBins b ON b.mainCategory = c.id AND b.deleted = 0 LEFT JOIN bsLocations l ON l.id = b.location GROUP BY c.id, c.name, c.color HAVING binCount > 0 ORDER BY binCount DESC "); $categories = $stmt->fetchAll(\PDO::FETCH_ASSOC); // Add health indicators foreach ($categories as &$cat) { $stalePercent = $cat['binCount'] > 0 ? ($cat['staleBins'] / $cat['binCount']) * 100 : 0; $cat['healthScore'] = max(0, round(100 - $stalePercent)); $cat['status'] = $stalePercent > 50 ? 'critical' : ($stalePercent > 25 ? 'warning' : 'healthy'); } return $categories; } } ``` #### Frontend: reports.js (Chart.js Integration) ```javascript // Backstock Reports JavaScript const BackstockReports = { typeNum: null, charts: {}, init: function(typeNum) { this.typeNum = typeNum; this.loadSummary(); }, // Load dashboard summary loadSummary: function() { $.get('/api/' + this.typeNum + '/backstock/reports/summary', (data) => { this.renderSummaryCards(data); }); }, // Render summary KPI cards renderSummaryCards: function(data) { $('#totalBins').text(data.totalBins); $('#avgAge').text(data.avgAge + ' days'); $('#staleBins').text(data.staleBins); $('#offSitePercent').text(data.offSitePercent + '%'); $('#healthScore').text(data.healthScore + '%'); }, // Load and render aging by category chart loadAgingChart: function(locationId, sortBy) { const params = new URLSearchParams(); if (locationId) params.append('location', locationId); if (sortBy) params.append('sort', sortBy); $.get('/api/' + this.typeNum + '/backstock/reports/aging?' + params, (data) => { this.renderAgingChart(data); }); }, // Render horizontal stacked bar chart (like ResaleAI) renderAgingChart: function(data) { const ctx = document.getElementById('agingChart').getContext('2d'); // Destroy existing chart if present if (this.charts.aging) { this.charts.aging.destroy(); } this.charts.aging = new Chart(ctx, { type: 'bar', data: { labels: data.labels, datasets: data.datasets }, options: { indexAxis: 'y', // Horizontal bars responsive: true, maintainAspectRatio: false, scales: { x: { stacked: true, title: { display: true, text: 'Number of Bins' } }, y: { stacked: true, ticks: { autoSkip: false } } }, plugins: { legend: { position: 'top', }, tooltip: { callbacks: { afterBody: function(context) { const idx = context[0].dataIndex; const total = data.meta.totals[idx]; const avgAge = data.meta.avgAges[idx]; return ['', 'Total: ' + total + ' bins', 'Avg Age: ' + avgAge + ' days']; } } } }, onClick: (event, elements) => { if (elements.length > 0) { const idx = elements[0].index; const categoryId = data.meta.categoryIds[idx]; BackstockReports.showCategoryDetail(categoryId, data.labels[idx]); } } } }); // Update summary stats this.renderAgingSummary(data.summary); }, // Render aging summary renderAgingSummary: function(summary) { $('#agingSummary').html(`
${summary.fresh} Fresh (0-90d) ${summary.freshPercent}%
${summary.aging} Aging (91-180d) ${summary.agingPercent}%
${summary.stale} Stale (181-365d) ${summary.stalePercent}%
${summary.veryStale} Critical (366+d) ${summary.veryStalePercent}%
`); }, // Show category detail modal when clicking a bar showCategoryDetail: function(categoryId, categoryName) { $.get('/api/' + this.typeNum + '/backstock/reports/aging/category/' + categoryId, (data) => { // Populate and show modal with bin list $('#categoryDetailModal .modal-title').text(categoryName + ' - ' + data.length + ' Bins'); let html = '' + ''; data.forEach(bin => { const statusClass = { 'fresh': 'success', 'aging': 'warning', 'stale': 'danger', 'very_stale': 'danger' }[bin.ageStatus]; html += ``; }); html += '
BinAgeLocationStatus
${bin.name} ${bin.age} days ${bin.locationName} ${bin.onsite ? '(On-site)' : '(Off-site)'} ${bin.ageStatus}
'; $('#categoryDetailModal .modal-body').html(html); $('#categoryDetailModal').modal('show'); }); }, // Export report exportReport: function(format, reportType) { window.location.href = '/api/' + this.typeNum + '/backstock/reports/export/' + format + '?report=' + reportType; } }; // Initialize on page load $(document).ready(function() { const typeNum = $('meta[name="typeNum"]').attr('content'); BackstockReports.init(typeNum); // Event handlers $('#loadAgingReport').click(function() { const location = $('#locationFilter').val(); const sort = $('#sortFilter').val(); BackstockReports.loadAgingChart(location, sort); }); $('#exportCSV').click(() => BackstockReports.exportReport('csv', 'aging')); $('#exportPDF').click(() => BackstockReports.exportReport('pdf', 'aging')); }); ``` --- ### 2.2 Better Bin Naming **Goal:** Auto-generate descriptive bin names and add notes field. #### Database Schema ```sql -- Migration: 2025_04_backstock_bin_enhancements.sql ALTER TABLE bsBins ADD COLUMN notes TEXT AFTER name, ADD COLUMN generatedName VARCHAR(255) AFTER notes, ADD COLUMN itemCount INT AFTER generatedName, ADD COLUMN estimatedValue DECIMAL(10,2) AFTER itemCount, ADD COLUMN lastAuditDate TIMESTAMP NULL, ADD COLUMN lastAuditBy INT, ADD FULLTEXT INDEX ft_notes (notes); ``` #### BinNamingService.php ```php store = $store; $this->db = dbConnectByName($store->getDbName()); } /** * Generate descriptive name from bin properties */ public function generateName(Bin $bin): string { $parts = []; // Add season if applicable $season = $this->detectSeason($bin); if ($season) { $parts[] = $season; } // Add main category if ($bin->mainCategory) { $parts[] = $this->getCategoryName($bin->mainCategory); } // Add size if detectable $size = $this->detectSize($bin); if ($size) { $parts[] = $size; } // Add bin number $parts[] = "#{$bin->name}"; return implode(' ', $parts); } /** * Generate names for all bins missing generated names */ public function generateAllNames(): int { $stmt = $this->db->query(" SELECT id FROM bsBins WHERE deleted = 0 AND (generatedName IS NULL OR generatedName = '') "); $count = 0; $factory = new BackstockFactory($this->store); while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { $bin = $factory->getBinByID($row['id']); if ($bin) { $name = $this->generateName($bin); $this->updateGeneratedName($bin->id, $name); $count++; } } return $count; } private function detectSeason(Bin $bin): ?string { $categoryName = $this->getCategoryName($bin->mainCategory); $seasonPatterns = [ 'Summer' => ['summer', 'swim', 'sandal', 'tank', 'short'], 'Winter' => ['winter', 'snow', 'coat', 'boot', 'fleece'], 'Spring' => ['spring', 'easter', 'rain'], 'Fall' => ['fall', 'halloween', 'autumn'], 'Christmas' => ['christmas', 'holiday'], ]; $lowerName = strtolower($categoryName); foreach ($seasonPatterns as $season => $patterns) { foreach ($patterns as $pattern) { if (strpos($lowerName, $pattern) !== false) { return $season; } } } // Check sub-categories foreach ($bin->categories as $cat) { $lowerCat = strtolower($cat['name']); foreach ($seasonPatterns as $season => $patterns) { foreach ($patterns as $pattern) { if (strpos($lowerCat, $pattern) !== false) { return $season; } } } } return null; } private function detectSize(Bin $bin): ?string { $categoryName = $this->getCategoryName($bin->mainCategory); // Size patterns if (preg_match('/(\d+[T]?)\s*(Boy|Girl)/i', $categoryName, $matches)) { return $matches[1] . ' ' . $matches[2]; } if (preg_match('/(0-3|3-6|6-9|12|18|24)\s*Month/i', $categoryName, $matches)) { return $matches[1] . 'M'; } return null; } private function getCategoryName(int $categoryId): string { $stmt = $this->db->prepare("SELECT name FROM bsCategories WHERE id = :id"); $stmt->execute(['id' => $categoryId]); return $stmt->fetchColumn() ?: ''; } private function updateGeneratedName(int $binId, string $name): void { $stmt = $this->db->prepare("UPDATE bsBins SET generatedName = :name WHERE id = :id"); $stmt->execute(['name' => $name, 'id' => $binId]); } } ``` --- ### 2.3 Mobile-First UI **Goal:** Responsive design optimized for warehouse workers on phones/tablets. #### File Structure ``` userfrosting/templates/themes/default/backstock/ ├── mobile/ │ ├── index.html # Mobile entry point │ ├── scan.html # QR/barcode scanner │ ├── bin-detail.html # Single bin view │ ├── quick-action.html # Quick action buttons │ └── search.html # Mobile search ├── css/ │ └── backstock-mobile.css # Mobile-specific styles └── js/ ├── mobile/ │ ├── scanner.js # Camera/scanner integration │ ├── offline.js # Service worker + IndexedDB │ └── touch.js # Touch gestures └── ... ``` #### Mobile Route (add to index.php) ```php // Mobile backstock interface $app->get('/m/:typeNum/backstock/?', function($typeNum) use ($app) { // Same auth as regular backstock if (!$app->user->checkStoreGroup($typeNum) || !getStoreStatus($typeNum)) { $app->notFound(); } if (!$app->user->checkAccess('uri_backstock')) { $app->notFound(); } $store = new \Store(); if (!$store->createStore($typeNum)) { $app->notFound(); } $app->render('backstock/mobile/index.html', [ 'page' => ['title' => 'Backstock'], 'store' => getStoreInfo($store), 'isMobile' => true ]); }); ``` #### Mobile Features 1. **Large Touch Targets** - Buttons minimum 48px 2. **Swipe Gestures** - Swipe to reveal actions 3. **QR Scanner** - Scan bin/location QR codes 4. **Offline Mode** - Cache bins locally, sync when connected 5. **Voice Input** - "Find summer swimwear size 10" --- ### 2.4 Enhanced Action Types **Goal:** More granular action tracking with quantities. #### Database Schema ```sql -- Migration: 2025_05_backstock_action_enhancements.sql -- Add new action types and quantity tracking ALTER TABLE bsActions MODIFY COLUMN action TINYINT NOT NULL COMMENT '0=Empty,1=Add,2=RemoveSome,3=RemoveAll,4=MovedToFloor,5=Received,6=Audited,7=Split,8=Consolidated', ADD COLUMN quantity INT AFTER categoryID, ADD COLUMN notes TEXT AFTER quantity, ADD COLUMN photoUrl VARCHAR(500) AFTER notes, ADD COLUMN relatedBinId INT AFTER photoUrl COMMENT 'For split/consolidate actions'; -- Action type reference table CREATE TABLE bsActionTypes ( id TINYINT PRIMARY KEY, name VARCHAR(50) NOT NULL, description VARCHAR(255), icon VARCHAR(50), requiresCategory TINYINT DEFAULT 0, requiresQuantity TINYINT DEFAULT 0, isFloorAction TINYINT DEFAULT 0 ); INSERT INTO bsActionTypes VALUES (0, 'Emptied', 'Removed everything from bin', 'fa-box-open', 0, 0, 0), (1, 'Added', 'Added items to bin', 'fa-plus', 1, 1, 0), (2, 'Removed Some', 'Removed some items', 'fa-minus', 1, 1, 0), (3, 'Removed All', 'Removed all of category', 'fa-times', 1, 0, 0), (4, 'Moved to Floor', 'Items placed on sales floor', 'fa-store', 1, 1, 1), (5, 'Received', 'New inventory received', 'fa-truck', 1, 1, 0), (6, 'Audited', 'Bin contents verified', 'fa-clipboard-check', 0, 1, 0), (7, 'Split', 'Bin split into multiple', 'fa-code-branch', 0, 0, 0), (8, 'Consolidated', 'Multiple bins merged', 'fa-compress-arrows-alt', 0, 0, 0); ``` #### Enhanced Action.php ```php employeeName ?: 'Unknown'; $category = $this->getCategoryName(); $quantity = $this->quantity ? " ({$this->quantity} items)" : ''; switch ($this->action) { case self::TYPE_EMPTIED: return "{$employee} emptied the bin"; case self::TYPE_ADDED: return "{$employee} added{$quantity} {$category}"; case self::TYPE_REMOVED_SOME: return "{$employee} removed{$quantity} {$category}"; case self::TYPE_REMOVED_ALL: return "{$employee} removed all {$category}"; case self::TYPE_MOVED_TO_FLOOR: return "{$employee} moved{$quantity} {$category} to floor"; case self::TYPE_RECEIVED: return "{$employee} received{$quantity} {$category}"; case self::TYPE_AUDITED: return "{$employee} audited bin" . ($this->quantity ? " - {$this->quantity} items" : ''); case self::TYPE_SPLIT: return "{$employee} split bin"; case self::TYPE_CONSOLIDATED: return "{$employee} consolidated bins"; default: return "{$employee} performed action"; } } public function isFloorAction(): bool { return $this->action === self::TYPE_MOVED_TO_FLOOR; } } ``` --- ### 2.5 Backstock Notes **Goal:** Create a notes/chat system specific to backstock where employees can share quick updates about what they've been doing, leave notes for the next shift, or document plans for upcoming seasonal work. This follows the existing Workbook Notes pattern. #### Concept ``` ┌──────────────────────────────────────────────────────────────────────────────┐ │ 📝 Backstock Notes [+ Add Note] │ ├──────────────────────────────────────────────────────────────────────────────┤ │ │ │ ┌─────────────────────────────────────────────────────────────────────────┐ │ │ │ 📌 PINNED │ │ │ │ ─────────────────────────────────────────────────────────────────────── │ │ │ │ Christmas Prep Plan Sarah M. │ │ │ │ Posted 2 days ago │ │ │ │ │ │ │ │ We need to start pulling Christmas bins by Nov 3rd. Focus on: │ │ │ │ - Christmas PJs (Aisle A, Shelf 1-3) │ │ │ │ - Holiday dresses (Storage Unit B) │ │ │ │ - Winter coats (Back Room, Right Wall) │ │ │ │ │ │ │ │ 👍 3 💬 2 comments [Edit] [Delete] │ │ │ └─────────────────────────────────────────────────────────────────────────┘ │ │ │ │ ┌─────────────────────────────────────────────────────────────────────────┐ │ │ │ Completed Halloween bins storage Mike T. │ │ │ │ Posted 5 hours ago │ │ │ │ │ │ │ │ Finished storing all Halloween bins back to storage unit. 12 bins total.│ │ │ │ Tagged with "Halloween" category. Shelf B4-B6. │ │ │ │ │ │ │ │ 👍 1 💬 0 comments │ │ │ └─────────────────────────────────────────────────────────────────────────┘ │ │ │ │ ┌─────────────────────────────────────────────────────────────────────────┐ │ │ │ Note for evening shift Lisa R. │ │ │ │ Posted 8 hours ago │ │ │ │ │ │ │ │ Started pulling Winter bins from storage. 8 more bins on cart in back │ │ │ │ room need to be processed. They're labeled with blue tape. │ │ │ │ │ │ │ │ 👍 2 💬 1 comment │ │ │ └─────────────────────────────────────────────────────────────────────────┘ │ │ │ │ [Load More Notes] │ │ │ └──────────────────────────────────────────────────────────────────────────────┘ ``` #### Database Schema ```sql -- Migration: 2025_XX_backstock_notes.sql -- Backstock notes table (per-store database) CREATE TABLE bsNotes ( id INT AUTO_INCREMENT PRIMARY KEY, authorEmployeeId INT, -- FK to employees table authorName VARCHAR(100), -- Fallback if employee deleted title VARCHAR(200), -- Optional note title content TEXT NOT NULL, -- Plain text content contentHtml TEXT, -- Rich text HTML (optional) isPinned TINYINT DEFAULT 0, -- Pinned notes appear first startDate DATE NOT NULL, -- When note becomes visible endDate DATE, -- When note expires (NULL = indefinite) createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deletedAt TIMESTAMP NULL, -- Soft delete INDEX idx_author (authorEmployeeId), INDEX idx_pinned (isPinned), INDEX idx_dates (startDate, endDate), INDEX idx_deleted (deletedAt) ); -- Note reactions (likes/acknowledgments) CREATE TABLE bsNote_Reactions ( id INT AUTO_INCREMENT PRIMARY KEY, noteId INT NOT NULL, employeeId INT NOT NULL, reactionType ENUM('like', 'heart') DEFAULT 'like', createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_note_employee (noteId, employeeId, reactionType), INDEX idx_note (noteId), FOREIGN KEY (noteId) REFERENCES bsNotes(id) ON DELETE CASCADE ); -- Note comments/replies CREATE TABLE bsNote_Comments ( id INT AUTO_INCREMENT PRIMARY KEY, noteId INT NOT NULL, employeeId INT NOT NULL, comment VARCHAR(2000) NOT NULL, createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_note (noteId), INDEX idx_employee (employeeId), FOREIGN KEY (noteId) REFERENCES bsNotes(id) ON DELETE CASCADE ); ``` #### Entity Classes **BackstockNote.php** ```php content) || !isset($this->startDate)) { $this->log->logError("BackstockNote::save - content and startDate required"); return false; } try { if ($this->id) { $stmt = $this->storeDB->prepare(" UPDATE bsNotes SET authorEmployeeId = :authorEmployeeId, authorName = :authorName, title = :title, content = :content, contentHtml = :contentHtml, isPinned = :isPinned, startDate = :startDate, endDate = :endDate WHERE id = :id "); return $stmt->execute([ ':id' => $this->id, ':authorEmployeeId' => $this->authorEmployeeId, ':authorName' => $this->authorName, ':title' => $this->title, ':content' => $this->content, ':contentHtml' => $this->contentHtml, ':isPinned' => $this->isPinned ? 1 : 0, ':startDate' => $this->startDate, ':endDate' => $this->endDate ]); } else { $stmt = $this->storeDB->prepare(" INSERT INTO bsNotes (authorEmployeeId, authorName, title, content, contentHtml, isPinned, startDate, endDate) VALUES (:authorEmployeeId, :authorName, :title, :content, :contentHtml, :isPinned, :startDate, :endDate) "); $result = $stmt->execute([ ':authorEmployeeId' => $this->authorEmployeeId, ':authorName' => $this->authorName, ':title' => $this->title, ':content' => $this->content, ':contentHtml' => $this->contentHtml, ':isPinned' => $this->isPinned ? 1 : 0, ':startDate' => $this->startDate, ':endDate' => $this->endDate ]); if ($result) { $this->id = (int) $this->storeDB->lastInsertId(); } return $result; } } catch (PDOException $e) { $this->log->logError("BackstockNote::save error: " . $e->getMessage()); return false; } } /** * Soft delete note */ public function delete(): bool { if (!$this->id) { return false; } try { $stmt = $this->storeDB->prepare(" UPDATE bsNotes SET deletedAt = NOW() WHERE id = :id "); return $stmt->execute([':id' => $this->id]); } catch (PDOException $e) { $this->log->logError("BackstockNote::delete error: " . $e->getMessage()); return false; } } /** * Convert to array for API responses */ public function toArray(): array { $authorName = $this->authorName; if (!$authorName && ($this->authorFirstName || $this->authorLastName)) { $authorName = trim(($this->authorFirstName ?? '') . ' ' . ($this->authorLastName ?? '')); } return [ 'id' => $this->id, 'authorEmployeeId' => $this->authorEmployeeId, 'authorFirstName' => $this->authorFirstName, 'authorLastName' => $this->authorLastName, 'authorName' => $authorName, 'title' => $this->title, 'content' => $this->content, 'contentHtml' => $this->contentHtml, 'isPinned' => (bool) $this->isPinned, 'startDate' => $this->startDate, 'endDate' => $this->endDate, 'reactionCount' => $this->reactionCount, 'commentCount' => $this->commentCount, 'userHasReacted' => (bool) $this->userHasReacted, 'createdAt' => $this->createdAt, 'updatedAt' => $this->updatedAt ]; } /** * Create from array (form data) */ public static function fromArray(array $data, \Store $store): self { $note = new self($store); $note->id = isset($data['id']) ? (int) $data['id'] : null; $note->authorEmployeeId = isset($data['authorEmployeeId']) ? (int) $data['authorEmployeeId'] : null; $note->authorName = $data['authorName'] ?? null; $note->title = $data['title'] ?? null; $note->content = $data['content']; $note->contentHtml = $data['contentHtml'] ?? null; $note->isPinned = isset($data['isPinned']) && filter_var($data['isPinned'], FILTER_VALIDATE_BOOLEAN); $note->startDate = $data['startDate']; $note->endDate = $data['endDate'] ?? null; return $note; } } ``` **BackstockNoteManager.php** ```php store = $store; } /** * Get paginated notes for infinite scroll feed */ public function getNotes(int $limit = 10, int $offset = 0, ?int $currentEmployeeId = null): array { try { $sql = " SELECT n.*, e.employeeFirstName as authorFirstName, e.employeeLastName as authorLastName, COALESCE(rc.reactionCount, 0) as reactionCount, COALESCE(cc.commentCount, 0) as commentCount, CASE WHEN ur.id IS NOT NULL THEN 1 ELSE 0 END as userHasReacted FROM bsNotes n LEFT JOIN employees e ON n.authorEmployeeId = e.employeeID LEFT JOIN ( SELECT noteId, COUNT(*) as reactionCount FROM bsNote_Reactions GROUP BY noteId ) rc ON n.id = rc.noteId LEFT JOIN ( SELECT noteId, COUNT(*) as commentCount FROM bsNote_Comments GROUP BY noteId ) cc ON n.id = cc.noteId "; $params = []; if ($currentEmployeeId !== null) { $sql .= " LEFT JOIN bsNote_Reactions ur ON n.id = ur.noteId AND ur.employeeId = :currentEmployeeId "; $params[':currentEmployeeId'] = $currentEmployeeId; } else { $sql .= " LEFT JOIN (SELECT NULL as id, NULL as noteId) ur ON 1=0 "; } $sql .= " WHERE n.deletedAt IS NULL ORDER BY n.isPinned DESC, n.createdAt DESC LIMIT :limit OFFSET :offset "; $stmt = $this->storeDB->prepare($sql); foreach ($params as $key => $value) { $stmt->bindValue($key, $value); } $stmt->bindValue(':limit', $limit + 1, PDO::PARAM_INT); $stmt->bindValue(':offset', $offset, PDO::PARAM_INT); $stmt->execute(); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); $hasMore = count($rows) > $limit; if ($hasMore) { array_pop($rows); } $notes = []; foreach ($rows as $row) { $notes[] = $this->hydrate($row); } return [ 'notes' => $notes, 'hasMore' => $hasMore ]; } catch (PDOException $e) { $this->log->logError("BackstockNoteManager::getNotes error: " . $e->getMessage()); return ['notes' => [], 'hasMore' => false]; } } /** * Get visible notes for today (respects startDate/endDate) */ public function getTodayNotes(?int $currentEmployeeId = null): array { $timezone = new \DateTimeZone($this->store->timezone); $now = new \DateTime('now', $timezone); $today = $now->format('Y-m-d'); try { $sql = " SELECT n.*, e.employeeFirstName as authorFirstName, e.employeeLastName as authorLastName, COALESCE(rc.reactionCount, 0) as reactionCount, COALESCE(cc.commentCount, 0) as commentCount, CASE WHEN ur.id IS NOT NULL THEN 1 ELSE 0 END as userHasReacted FROM bsNotes n LEFT JOIN employees e ON n.authorEmployeeId = e.employeeID LEFT JOIN ( SELECT noteId, COUNT(*) as reactionCount FROM bsNote_Reactions GROUP BY noteId ) rc ON n.id = rc.noteId LEFT JOIN ( SELECT noteId, COUNT(*) as commentCount FROM bsNote_Comments GROUP BY noteId ) cc ON n.id = cc.noteId "; $params = [':today' => $today]; if ($currentEmployeeId !== null) { $sql .= " LEFT JOIN bsNote_Reactions ur ON n.id = ur.noteId AND ur.employeeId = :currentEmployeeId "; $params[':currentEmployeeId'] = $currentEmployeeId; } else { $sql .= " LEFT JOIN (SELECT NULL as id, NULL as noteId) ur ON 1=0 "; } $sql .= " WHERE n.deletedAt IS NULL AND n.startDate <= :today AND (n.endDate IS NULL OR n.endDate >= :today) ORDER BY n.isPinned DESC, n.createdAt DESC "; $stmt = $this->storeDB->prepare($sql); $stmt->execute($params); $notes = []; while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $notes[] = $this->hydrate($row); } return $notes; } catch (PDOException $e) { $this->log->logError("BackstockNoteManager::getTodayNotes error: " . $e->getMessage()); return []; } } /** * Get a single note by ID */ public function getById(int $id, ?int $currentEmployeeId = null): ?BackstockNote { try { $sql = " SELECT n.*, e.employeeFirstName as authorFirstName, e.employeeLastName as authorLastName, COALESCE(rc.reactionCount, 0) as reactionCount, COALESCE(cc.commentCount, 0) as commentCount, CASE WHEN ur.id IS NOT NULL THEN 1 ELSE 0 END as userHasReacted FROM bsNotes n LEFT JOIN employees e ON n.authorEmployeeId = e.employeeID LEFT JOIN ( SELECT noteId, COUNT(*) as reactionCount FROM bsNote_Reactions GROUP BY noteId ) rc ON n.id = rc.noteId LEFT JOIN ( SELECT noteId, COUNT(*) as commentCount FROM bsNote_Comments GROUP BY noteId ) cc ON n.id = cc.noteId "; $params = [':id' => $id]; if ($currentEmployeeId !== null) { $sql .= " LEFT JOIN bsNote_Reactions ur ON n.id = ur.noteId AND ur.employeeId = :currentEmployeeId "; $params[':currentEmployeeId'] = $currentEmployeeId; } else { $sql .= " LEFT JOIN (SELECT NULL as id, NULL as noteId) ur ON 1=0 "; } $sql .= " WHERE n.id = :id AND n.deletedAt IS NULL"; $stmt = $this->storeDB->prepare($sql); $stmt->execute($params); $row = $stmt->fetch(PDO::FETCH_ASSOC); if (!$row) { return null; } return $this->hydrate($row); } catch (PDOException $e) { $this->log->logError("BackstockNoteManager::getById error: " . $e->getMessage()); return null; } } /** * Add reaction to a note */ public function addReaction(int $noteId, int $employeeId, string $reactionType = 'like'): bool { try { $stmt = $this->storeDB->prepare(" INSERT IGNORE INTO bsNote_Reactions (noteId, employeeId, reactionType) VALUES (:noteId, :employeeId, :reactionType) "); return $stmt->execute([ ':noteId' => $noteId, ':employeeId' => $employeeId, ':reactionType' => $reactionType ]); } catch (PDOException $e) { $this->log->logError("BackstockNoteManager::addReaction error: " . $e->getMessage()); return false; } } /** * Remove reaction from a note */ public function removeReaction(int $noteId, int $employeeId): bool { try { $stmt = $this->storeDB->prepare(" DELETE FROM bsNote_Reactions WHERE noteId = :noteId AND employeeId = :employeeId "); return $stmt->execute([ ':noteId' => $noteId, ':employeeId' => $employeeId ]); } catch (PDOException $e) { $this->log->logError("BackstockNoteManager::removeReaction error: " . $e->getMessage()); return false; } } /** * Add comment to a note */ public function addComment(int $noteId, int $employeeId, string $comment): ?int { try { $stmt = $this->storeDB->prepare(" INSERT INTO bsNote_Comments (noteId, employeeId, comment) VALUES (:noteId, :employeeId, :comment) "); $result = $stmt->execute([ ':noteId' => $noteId, ':employeeId' => $employeeId, ':comment' => substr($comment, 0, 2000) ]); return $result ? (int) $this->storeDB->lastInsertId() : null; } catch (PDOException $e) { $this->log->logError("BackstockNoteManager::addComment error: " . $e->getMessage()); return null; } } /** * Get comments for a note */ public function getComments(int $noteId): array { try { $stmt = $this->storeDB->prepare(" SELECT c.*, e.employeeFirstName, e.employeeLastName FROM bsNote_Comments c LEFT JOIN employees e ON c.employeeId = e.employeeID WHERE c.noteId = :noteId ORDER BY c.createdAt ASC "); $stmt->execute([':noteId' => $noteId]); $comments = []; while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $authorName = trim(($row['employeeFirstName'] ?? '') . ' ' . ($row['employeeLastName'] ?? '')); $comments[] = [ 'id' => (int) $row['id'], 'noteId' => (int) $row['noteId'], 'employeeId' => (int) $row['employeeId'], 'authorName' => $authorName ?: 'Unknown', 'comment' => $row['comment'], 'createdAt' => $row['createdAt'] ]; } return $comments; } catch (PDOException $e) { $this->log->logError("BackstockNoteManager::getComments error: " . $e->getMessage()); return []; } } /** * Hydrate a BackstockNote from database row */ private function hydrate(array $row): BackstockNote { $note = new BackstockNote($this->store); $note->id = (int) $row['id']; $note->authorEmployeeId = isset($row['authorEmployeeId']) ? (int) $row['authorEmployeeId'] : null; $note->authorName = $row['authorName'] ?? null; $note->title = $row['title']; $note->content = $row['content']; $note->contentHtml = $row['contentHtml']; $note->isPinned = (bool) $row['isPinned']; $note->startDate = $row['startDate']; $note->endDate = $row['endDate']; $note->createdAt = $row['createdAt']; $note->updatedAt = $row['updatedAt']; $note->deletedAt = $row['deletedAt'] ?? null; // Joined fields $note->authorFirstName = $row['authorFirstName'] ?? null; $note->authorLastName = $row['authorLastName'] ?? null; $note->reactionCount = isset($row['reactionCount']) ? (int) $row['reactionCount'] : 0; $note->commentCount = isset($row['commentCount']) ? (int) $row['commentCount'] : 0; $note->userHasReacted = isset($row['userHasReacted']) ? (bool) $row['userHasReacted'] : false; return $note; } } ``` #### API Routes **routes/groups/backstock-notes.php** ```php group('/api/:typeNum/backstock/notes', function() use ($app) { /** * GET /api/:typeNum/backstock/notes/ * Get paginated notes for infinite scroll * * Query params: * - limit: int (default: 10) * - offset: int (default: 0) * - employeeId: int (for reaction status) */ $app->get('/?', function($typeNum) use ($app) { $store = checkAccessAndReturnStoreObject($app, $typeNum, 'uri_backstock'); if (!$store) { $app->halt(403, json_encode(['error' => 'Access denied'])); return; } $limit = (int) ($app->request->get('limit') ?? 10); $offset = (int) ($app->request->get('offset') ?? 0); $employeeId = $app->request->get('employeeId') ? (int) $app->request->get('employeeId') : null; $manager = new BackstockNoteManager($store); $result = $manager->getNotes($limit, $offset, $employeeId); $app->response->headers->set('Content-Type', 'application/json'); echo json_encode([ 'notes' => array_map(fn($n) => $n->toArray(), $result['notes']), 'hasMore' => $result['hasMore'] ]); })->conditions(['typeNum' => '[a-z]{2}\d+']); /** * POST /api/:typeNum/backstock/notes/ * Create a new note */ $app->post('/?', function($typeNum) use ($app) { $store = checkAccessAndReturnStoreObject($app, $typeNum, 'uri_backstock'); if (!$store) { $app->halt(403, json_encode(['error' => 'Access denied'])); return; } $data = json_decode($app->request->getBody(), true); // Set default start date to today if not provided if (empty($data['startDate'])) { $timezone = new \DateTimeZone($store->timezone); $data['startDate'] = (new \DateTime('now', $timezone))->format('Y-m-d'); } $note = BackstockNote::fromArray($data, $store); if ($note->save()) { $app->response->headers->set('Content-Type', 'application/json'); echo json_encode(['success' => true, 'noteId' => $note->id]); } else { $app->halt(400, json_encode(['error' => 'Failed to create note'])); } })->conditions(['typeNum' => '[a-z]{2}\d+']); /** * PUT /api/:typeNum/backstock/notes/:noteId/ * Update an existing note */ $app->put('/:noteId/?', function($typeNum, $noteId) use ($app) { $store = checkAccessAndReturnStoreObject($app, $typeNum, 'uri_backstock'); if (!$store) { $app->halt(403, json_encode(['error' => 'Access denied'])); return; } $manager = new BackstockNoteManager($store); $note = $manager->getById((int) $noteId); if (!$note) { $app->halt(404, json_encode(['error' => 'Note not found'])); return; } $data = json_decode($app->request->getBody(), true); // Update fields if (isset($data['title'])) $note->title = $data['title']; if (isset($data['content'])) $note->content = $data['content']; if (isset($data['contentHtml'])) $note->contentHtml = $data['contentHtml']; if (isset($data['isPinned'])) $note->isPinned = filter_var($data['isPinned'], FILTER_VALIDATE_BOOLEAN); if (isset($data['startDate'])) $note->startDate = $data['startDate']; if (isset($data['endDate'])) $note->endDate = $data['endDate']; if ($note->save()) { $app->response->headers->set('Content-Type', 'application/json'); echo json_encode(['success' => true]); } else { $app->halt(400, json_encode(['error' => 'Failed to update note'])); } })->conditions(['typeNum' => '[a-z]{2}\d+', 'noteId' => '\d+']); /** * DELETE /api/:typeNum/backstock/notes/:noteId/ * Soft delete a note */ $app->delete('/:noteId/?', function($typeNum, $noteId) use ($app) { $store = checkAccessAndReturnStoreObject($app, $typeNum, 'uri_backstock'); if (!$store) { $app->halt(403, json_encode(['error' => 'Access denied'])); return; } $manager = new BackstockNoteManager($store); $note = $manager->getById((int) $noteId); if (!$note) { $app->halt(404, json_encode(['error' => 'Note not found'])); return; } if ($note->delete()) { $app->response->headers->set('Content-Type', 'application/json'); echo json_encode(['success' => true]); } else { $app->halt(400, json_encode(['error' => 'Failed to delete note'])); } })->conditions(['typeNum' => '[a-z]{2}\d+', 'noteId' => '\d+']); /** * POST /api/:typeNum/backstock/notes/:noteId/react/ * Add a reaction to a note */ $app->post('/:noteId/react/?', function($typeNum, $noteId) use ($app) { $store = checkAccessAndReturnStoreObject($app, $typeNum, 'uri_backstock'); if (!$store) { $app->halt(403, json_encode(['error' => 'Access denied'])); return; } $data = json_decode($app->request->getBody(), true); $employeeId = (int) ($data['employeeId'] ?? 0); $reactionType = $data['reactionType'] ?? 'like'; if (!$employeeId) { $app->halt(400, json_encode(['error' => 'employeeId required'])); return; } $manager = new BackstockNoteManager($store); if ($manager->addReaction((int) $noteId, $employeeId, $reactionType)) { $app->response->headers->set('Content-Type', 'application/json'); echo json_encode(['success' => true]); } else { $app->halt(400, json_encode(['error' => 'Failed to add reaction'])); } })->conditions(['typeNum' => '[a-z]{2}\d+', 'noteId' => '\d+']); /** * DELETE /api/:typeNum/backstock/notes/:noteId/react/ * Remove a reaction from a note */ $app->delete('/:noteId/react/?', function($typeNum, $noteId) use ($app) { $store = checkAccessAndReturnStoreObject($app, $typeNum, 'uri_backstock'); if (!$store) { $app->halt(403, json_encode(['error' => 'Access denied'])); return; } $data = json_decode($app->request->getBody(), true); $employeeId = (int) ($data['employeeId'] ?? 0); if (!$employeeId) { $app->halt(400, json_encode(['error' => 'employeeId required'])); return; } $manager = new BackstockNoteManager($store); if ($manager->removeReaction((int) $noteId, $employeeId)) { $app->response->headers->set('Content-Type', 'application/json'); echo json_encode(['success' => true]); } else { $app->halt(400, json_encode(['error' => 'Failed to remove reaction'])); } })->conditions(['typeNum' => '[a-z]{2}\d+', 'noteId' => '\d+']); /** * POST /api/:typeNum/backstock/notes/:noteId/comment/ * Add a comment to a note */ $app->post('/:noteId/comment/?', function($typeNum, $noteId) use ($app) { $store = checkAccessAndReturnStoreObject($app, $typeNum, 'uri_backstock'); if (!$store) { $app->halt(403, json_encode(['error' => 'Access denied'])); return; } $data = json_decode($app->request->getBody(), true); $employeeId = (int) ($data['employeeId'] ?? 0); $comment = trim($data['comment'] ?? ''); if (!$employeeId || !$comment) { $app->halt(400, json_encode(['error' => 'employeeId and comment required'])); return; } $manager = new BackstockNoteManager($store); $commentId = $manager->addComment((int) $noteId, $employeeId, $comment); if ($commentId) { $app->response->headers->set('Content-Type', 'application/json'); echo json_encode(['success' => true, 'commentId' => $commentId]); } else { $app->halt(400, json_encode(['error' => 'Failed to add comment'])); } })->conditions(['typeNum' => '[a-z]{2}\d+', 'noteId' => '\d+']); /** * GET /api/:typeNum/backstock/notes/:noteId/comments/ * Get all comments for a note */ $app->get('/:noteId/comments/?', function($typeNum, $noteId) use ($app) { $store = checkAccessAndReturnStoreObject($app, $typeNum, 'uri_backstock'); if (!$store) { $app->halt(403, json_encode(['error' => 'Access denied'])); return; } $manager = new BackstockNoteManager($store); $comments = $manager->getComments((int) $noteId); $app->response->headers->set('Content-Type', 'application/json'); echo json_encode(['comments' => $comments]); })->conditions(['typeNum' => '[a-z]{2}\d+', 'noteId' => '\d+']); }); ``` #### Frontend Templates **templates/themes/default/backstock/partials/notes-panel.html** ```twig {# Backstock Notes Panel #}

Backstock Notes

Loading notes...
{# Add/Edit Backstock Note Modal #} {# Handlebars Templates #} {% raw %} {% endraw %} ``` #### JavaScript **js/backstock-notes.js** ```javascript /** * Backstock Notes Manager * * Handles loading, displaying, and interacting with backstock-specific notes. * Follows the same pattern as the workbook notes system. */ (function($) { 'use strict'; var BackstockNotes = { typeNum: null, employeeId: null, offset: 0, limit: 10, hasMore: true, noteTemplate: null, commentTemplate: null, init: function(typeNum, employeeId) { this.typeNum = typeNum; this.employeeId = employeeId; // Compile Handlebars templates this.noteTemplate = Handlebars.compile($('#backstock-note-template').html()); this.commentTemplate = Handlebars.compile($('#backstock-comment-template').html()); // Register Handlebars helpers this.registerHelpers(); // Bind events this.bindEvents(); // Load initial notes this.loadNotes(); }, registerHelpers: function() { Handlebars.registerHelper('ifEquals', function(a, b, options) { return a === b ? options.fn(this) : options.inverse(this); }); }, bindEvents: function() { var self = this; // Load more button $('#loadMoreBackstockNotes').on('click', function() { self.loadNotes(); }); // Save note $('#saveBackstockNoteBtn').on('click', function() { self.saveNote(); }); // Note actions (delegated) $('#backstockNotesList').on('click', '.reaction-btn', function() { self.toggleReaction($(this).data('note-id')); }); $('#backstockNotesList').on('click', '.comments-btn', function() { self.toggleComments($(this).data('note-id')); }); $('#backstockNotesList').on('click', '.edit-backstock-note-btn', function() { self.editNote($(this).data('note-id')); }); $('#backstockNotesList').on('click', '.delete-backstock-note-btn', function() { self.deleteNote($(this).data('note-id')); }); $('#backstockNotesList').on('keypress', '.backstock-comment-input', function(e) { if (e.which === 13) { self.submitComment($(this).data('note-id'), $(this).val()); $(this).val(''); } }); $('#backstockNotesList').on('click', '.submit-backstock-comment', function() { var noteId = $(this).data('note-id'); var $input = $(this).siblings('.backstock-comment-input'); self.submitComment(noteId, $input.val()); $input.val(''); }); // Reset form on modal close $('#addBackstockNoteModal').on('hidden.bs.modal', function() { $('#backstockNoteForm')[0].reset(); $('#backstockNoteId').val(''); $('#backstockNoteModalTitle').html(' Add Note'); }); }, loadNotes: function() { var self = this; $.ajax({ url: '/api/' + this.typeNum + '/backstock/notes/', method: 'GET', data: { limit: this.limit, offset: this.offset, employeeId: this.employeeId }, success: function(response) { $('#backstockNotesLoading').hide(); if (response.notes.length === 0 && self.offset === 0) { $('#backstockNotesEmpty').show(); return; } $('#backstockNotesEmpty').hide(); response.notes.forEach(function(note) { note.createdAtRelative = self.formatRelativeTime(note.createdAt); note.isAuthor = (note.authorEmployeeId === self.employeeId); var html = self.noteTemplate(note); $('#backstockNotesList').append(html); }); self.offset += response.notes.length; self.hasMore = response.hasMore; if (self.hasMore) { $('#backstockNotesLoadMore').show(); } else { $('#backstockNotesLoadMore').hide(); } }, error: function() { $('#backstockNotesLoading').html('Failed to load notes'); } }); }, saveNote: function() { var self = this; var noteId = $('#backstockNoteId').val(); var data = { title: $('#backstockNoteTitle').val(), content: $('#backstockNoteContent').val(), startDate: $('#backstockNoteStartDate').val() || null, endDate: $('#backstockNoteEndDate').val() || null, isPinned: $('#backstockNotePinned').is(':checked'), authorEmployeeId: this.employeeId }; var method = noteId ? 'PUT' : 'POST'; var url = '/api/' + this.typeNum + '/backstock/notes/' + (noteId ? noteId + '/' : ''); $.ajax({ url: url, method: method, contentType: 'application/json', data: JSON.stringify(data), success: function() { $('#addBackstockNoteModal').modal('hide'); // Reload notes self.offset = 0; $('#backstockNotesList').empty(); self.loadNotes(); }, error: function(xhr) { alert('Failed to save note: ' + (xhr.responseJSON?.error || 'Unknown error')); } }); }, editNote: function(noteId) { var self = this; $.ajax({ url: '/api/' + this.typeNum + '/backstock/notes/' + noteId + '/?employeeId=' + this.employeeId, method: 'GET', success: function(response) { var note = response.notes ? response.notes[0] : response; $('#backstockNoteId').val(note.id); $('#backstockNoteTitle').val(note.title); $('#backstockNoteContent').val(note.content); $('#backstockNoteStartDate').val(note.startDate); $('#backstockNoteEndDate').val(note.endDate); $('#backstockNotePinned').prop('checked', note.isPinned); $('#backstockNoteModalTitle').html(' Edit Note'); $('#addBackstockNoteModal').modal('show'); } }); }, deleteNote: function(noteId) { var self = this; if (!confirm('Are you sure you want to delete this note?')) { return; } $.ajax({ url: '/api/' + this.typeNum + '/backstock/notes/' + noteId + '/', method: 'DELETE', success: function() { $('[data-note-id="' + noteId + '"]').fadeOut(function() { $(this).remove(); }); }, error: function() { alert('Failed to delete note'); } }); }, toggleReaction: function(noteId) { var self = this; var $btn = $('[data-note-id="' + noteId + '"].reaction-btn'); var hasReacted = $btn.hasClass('active'); var method = hasReacted ? 'DELETE' : 'POST'; $.ajax({ url: '/api/' + this.typeNum + '/backstock/notes/' + noteId + '/react/', method: method, contentType: 'application/json', data: JSON.stringify({ employeeId: this.employeeId }), success: function() { var $count = $btn.find('.reaction-count'); var count = parseInt($count.text()) || 0; if (hasReacted) { $btn.removeClass('active').css('color', '#999'); $count.text(Math.max(0, count - 1)); } else { $btn.addClass('active').css('color', '#337ab7'); $count.text(count + 1); } } }); }, toggleComments: function(noteId) { var self = this; var $section = $('#backstockNoteComments' + noteId); if ($section.is(':visible')) { $section.slideUp(); return; } // Load comments if not already loaded var $list = $('#backstockCommentsFor' + noteId); if ($list.children().length === 0) { $.ajax({ url: '/api/' + this.typeNum + '/backstock/notes/' + noteId + '/comments/', method: 'GET', success: function(response) { response.comments.forEach(function(comment) { comment.createdAtRelative = self.formatRelativeTime(comment.createdAt); var html = self.commentTemplate(comment); $list.append(html); }); } }); } $section.slideDown(); }, submitComment: function(noteId, comment) { var self = this; if (!comment.trim()) return; $.ajax({ url: '/api/' + this.typeNum + '/backstock/notes/' + noteId + '/comment/', method: 'POST', contentType: 'application/json', data: JSON.stringify({ employeeId: this.employeeId, comment: comment }), success: function() { // Reload comments var $list = $('#backstockCommentsFor' + noteId); $list.empty(); $.ajax({ url: '/api/' + self.typeNum + '/backstock/notes/' + noteId + '/comments/', method: 'GET', success: function(response) { response.comments.forEach(function(c) { c.createdAtRelative = self.formatRelativeTime(c.createdAt); var html = self.commentTemplate(c); $list.append(html); }); } }); // Update comment count var $btn = $('[data-note-id="' + noteId + '"].comments-btn'); var $count = $btn.find('.comment-count'); $count.text(parseInt($count.text()) + 1); } }); }, formatRelativeTime: function(dateString) { var date = new Date(dateString); var now = new Date(); var diffMs = now - date; var diffMins = Math.floor(diffMs / 60000); var diffHours = Math.floor(diffMs / 3600000); var diffDays = Math.floor(diffMs / 86400000); if (diffMins < 1) return 'Just now'; if (diffMins < 60) return diffMins + ' min ago'; if (diffHours < 24) return diffHours + ' hours ago'; if (diffDays < 7) return diffDays + ' days ago'; return date.toLocaleDateString(); } }; // Export to global scope window.BackstockNotes = BackstockNotes; })(jQuery); ``` #### Integration with Backstock Page Add to **templates/themes/default/backstock/home.html**: ```twig {# Add Notes Panel to sidebar or main content area #} {% include 'themes/default/backstock/partials/notes-panel.html' %} {# Initialize in script section #} ``` #### Key Differences from Workbook Notes | Feature | Workbook Notes | Backstock Notes | |---------|---------------|-----------------| | Context | General store communication | Backstock-specific updates | | Visibility | Time-based + manager-only | Time-based only | | Rich Text | Full HTML editor | Simple textarea | | Location | Workbook dashboard | Backstock page sidebar | | Reactions | Like + Heart | Like only (simpler) | | Tables | workbook_notes | bsNotes | --- ## File Structure Overview ``` userfrosting/ ├── models/Class/Backstock/ │ ├── Backstock.php # Base class (existing) │ ├── Bin.php # Enhanced │ ├── Action.php # Enhanced │ ├── Category.php # Enhanced (existing) │ ├── Location.php # Enhanced (existing) │ ├── BackstockFactory.php # Existing │ ├── BinNamingService.php # NEW │ ├── Season/ │ │ ├── SeasonConfig.php # NEW │ │ ├── SeasonConfigRepository.php # NEW │ │ ├── SeasonConfigMapper.php # NEW │ │ ├── CategorySeason.php # NEW │ │ ├── CategorySeasonRepository.php # NEW │ │ ├── SeasonAlert.php # NEW │ │ ├── SeasonAlertRepository.php # NEW │ │ └── SeasonService.php # NEW │ ├── Category/ │ │ ├── CategoryRepository.php # NEW (enhanced) │ │ ├── CategoryService.php # NEW │ │ ├── CategoryMerger.php # NEW │ │ └── CategoryHierarchy.php # NEW │ ├── Location/ │ │ ├── LocationRepository.php # NEW (enhanced) │ │ ├── LocationService.php # NEW │ │ └── LocationHierarchy.php # NEW │ ├── Reports/ │ │ ├── ReportService.php # NEW │ │ ├── AgeDistributionReport.php # NEW │ │ ├── SeasonalReadinessReport.php # NEW │ │ ├── ActivityReport.php # NEW │ │ └── StaleInventoryReport.php # NEW │ └── Notes/ │ ├── BackstockNote.php # NEW │ └── BackstockNoteManager.php # NEW │ ├── controllers/Backstock/ │ ├── BackstockController.php # Existing │ ├── SeasonController.php # NEW │ ├── CategoryController.php # NEW │ ├── LocationController.php # NEW │ └── ReportController.php # NEW │ ├── routes/groups/ │ ├── backstock.php # Enhanced with new routes │ └── backstock-notes.php # NEW - Notes API routes │ └── templates/themes/default/backstock/ ├── home.html # Enhanced ├── seasons/ │ ├── config.html # NEW │ ├── dashboard-widget.html # NEW │ └── modals/ # NEW ├── categories/ │ ├── manage.html # NEW │ ├── hierarchy.html # NEW │ └── cleanup.html # NEW ├── locations/ │ ├── manage.html # NEW │ ├── tree.html # NEW │ └── labels.html # NEW ├── reports/ │ ├── dashboard.html # NEW │ └── partials/ # NEW ├── mobile/ │ ├── index.html # NEW │ ├── scan.html # NEW │ └── ... # NEW ├── partials/ │ └── notes-panel.html # NEW - Notes feed UI └── js/ ├── main.js # Existing ├── seasons.js # NEW ├── categoryManager.js # NEW ├── locationManager.js # NEW ├── reports.js # NEW ├── backstock-notes.js # NEW - Notes manager └── mobile/ # NEW ``` --- ## Database Migrations All migrations use the existing conductor system. JSON migration files are stored in `userfrosting/migrations/input/` and run via `php conductor run`. ### Migration Files | Migration File | Description | Priority | |----------------|-------------|----------| | `20251202_001_backstock_seasonal_events.json` | Seasonal events tables (bsEvents, bsEvent_Categories, etc.) | High | | `20251202_002_backstock_category_hierarchy.json` | Category hierarchy enhancements | High | | `20251202_003_backstock_location_hierarchy.json` | Location hierarchy with parent locations | High | | `20251202_004_backstock_bin_enhancements.json` | Bin notes, naming conventions | Medium | | `20251202_005_backstock_action_enhancements.json` | Enhanced action types and quantities | Medium | | `20251202_006_backstock_notes.json` | Notes/chat system for backstock | Medium | ### Example Migration: Backstock Notes **File:** `userfrosting/migrations/input/20251202_006_backstock_notes.json` ```json [ { "type": "create_table", "description": "Create bsNotes table for backstock-specific notes", "database": "{{store}}", "check_query": "SHOW TABLES LIKE 'bsNotes'", "sql": "CREATE TABLE `bsNotes` (\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `authorEmployeeId` int(10) unsigned DEFAULT NULL,\n `authorName` varchar(100) DEFAULT NULL COMMENT 'Fallback if employee deleted',\n `title` varchar(200) DEFAULT NULL,\n `content` text NOT NULL,\n `contentHtml` text DEFAULT NULL,\n `isPinned` tinyint(1) unsigned DEFAULT 0,\n `startDate` date NOT NULL,\n `endDate` date DEFAULT NULL,\n `createdAt` timestamp NOT NULL DEFAULT current_timestamp(),\n `updatedAt` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),\n `deletedAt` timestamp NULL DEFAULT NULL COMMENT 'Soft delete',\n PRIMARY KEY (`id`),\n KEY `idx_author` (`authorEmployeeId`),\n KEY `idx_pinned` (`isPinned`),\n KEY `idx_dates` (`startDate`, `endDate`),\n KEY `idx_deleted` (`deletedAt`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Backstock notes and announcements'" }, { "type": "create_table", "description": "Create bsNote_Reactions table for note likes", "database": "{{store}}", "check_query": "SHOW TABLES LIKE 'bsNote_Reactions'", "sql": "CREATE TABLE `bsNote_Reactions` (\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `noteId` int(10) unsigned NOT NULL,\n `employeeId` int(10) unsigned NOT NULL,\n `reactionType` enum('like','heart') DEFAULT 'like',\n `createdAt` timestamp NOT NULL DEFAULT current_timestamp(),\n PRIMARY KEY (`id`),\n UNIQUE KEY `uk_note_employee` (`noteId`, `employeeId`, `reactionType`),\n KEY `idx_note` (`noteId`),\n CONSTRAINT `fk_bsNote_Reactions_noteId` FOREIGN KEY (`noteId`) REFERENCES `bsNotes` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Reactions on backstock notes'" }, { "type": "create_table", "description": "Create bsNote_Comments table for note comments", "database": "{{store}}", "check_query": "SHOW TABLES LIKE 'bsNote_Comments'", "sql": "CREATE TABLE `bsNote_Comments` (\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `noteId` int(10) unsigned NOT NULL,\n `employeeId` int(10) unsigned NOT NULL,\n `comment` varchar(2000) NOT NULL,\n `createdAt` timestamp NOT NULL DEFAULT current_timestamp(),\n PRIMARY KEY (`id`),\n KEY `idx_note` (`noteId`),\n KEY `idx_employee` (`employeeId`),\n CONSTRAINT `fk_bsNote_Comments_noteId` FOREIGN KEY (`noteId`) REFERENCES `bsNotes` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Comments on backstock notes'" } ] ``` ### Running Migrations ```bash # Run all pending migrations php conductor run # The system automatically: # 1. Reads all JSON files from migrations/input/ # 2. Checks if each operation has been applied (via migration_log) # 3. Applies {{store}} migrations to ALL store databases # 4. Logs results (success/skipped/error) ``` ### Migration Tracking The system uses `migration_log` table to track applied migrations per operation per store, preventing duplicate applications --- ## Testing Strategy ### Unit Tests ``` tests/ ├── Backstock/ │ ├── SeasonServiceTest.php │ ├── CategoryServiceTest.php │ ├── CategoryMergerTest.php │ ├── LocationServiceTest.php │ ├── ReportServiceTest.php │ └── BinNamingServiceTest.php ``` ### Integration Tests - API endpoint tests for all new routes - Database transaction rollback tests - Multi-store isolation tests ### Manual Testing Checklist - [ ] Season configuration CRUD - [ ] Category merge with bins - [ ] Location hierarchy navigation - [ ] Report generation accuracy - [ ] Mobile UI on actual devices - [ ] Offline mode sync - [ ] QR code scanning --- ## Implementation Timeline ### Phase 1: Foundation (Week 1-2) - [ ] Database migrations - [ ] Base entity classes - [ ] Repository implementations ### Phase 2: Seasonal Intelligence (Week 3-4) - [ ] SeasonService implementation - [ ] Season configuration UI - [ ] Dashboard alerts widget - [ ] API endpoints ### Phase 3: Category & Location (Week 5-6) - [ ] Category hierarchy - [ ] Category cleanup tools - [ ] Location hierarchy - [ ] Location labels/QR ### Phase 4: Reporting (Week 7-8) - [ ] ReportService implementation - [ ] Chart.js integration - [ ] Report dashboard UI - [ ] Export functionality ### Phase 5: Mobile & Actions (Week 9-10) - [ ] Mobile UI templates - [ ] Enhanced action types - [ ] Offline support - [ ] QR scanner integration ### Phase 6: Testing & Polish (Week 11-12) - [ ] Unit tests - [ ] Integration tests - [ ] Performance optimization - [ ] Documentation