9c220a4194
New Plugins: - USB plugin: Device checkout/checkin with employee lookup, checkout history - Notifications plugin: Announcements with types, scheduling, shopfloor display - Network plugin: Network device management with subnets and VLANs - Equipment and Computers plugins: Asset type separation Frontend: - EmployeeSearch component: Reusable employee lookup with autocomplete - USB views: List, detail, checkout/checkin modals - Notifications views: List, form with recognition mode - Network views: Device list, detail, form - Calendar view with FullCalendar integration - Shopfloor and TV dashboard views - Reports index page - Map editor for asset positioning - Light/dark mode fixes for map tooltips Backend: - Employee search API with external lookup service - Collector API for PowerShell data collection - Reports API endpoints - Slides API for TV dashboard - Fixed AppVersion model (removed BaseModel inheritance) - Added checkout_name column to usbcheckouts table Styling: - Unified detail page styles - Improved pagination (page numbers instead of prev/next) - Dark/light mode theme improvements Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
89 lines
4.4 KiB
SQL
89 lines
4.4 KiB
SQL
-- =============================================================================
|
|
-- Legacy Schema Migration Script
|
|
-- Adds missing columns to make VBScript ShopDB compatible with Flask ShopDB
|
|
-- =============================================================================
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- appowners table
|
|
-- -----------------------------------------------------------------------------
|
|
ALTER TABLE appowners
|
|
ADD COLUMN IF NOT EXISTS email VARCHAR(100) NULL,
|
|
ADD COLUMN IF NOT EXISTS phone VARCHAR(50) NULL,
|
|
ADD COLUMN IF NOT EXISTS createddate DATETIME DEFAULT NOW(),
|
|
ADD COLUMN IF NOT EXISTS modifieddate DATETIME DEFAULT NOW(),
|
|
ADD COLUMN IF NOT EXISTS isactive TINYINT(1) DEFAULT 1;
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- pctypes / computertypes alignment
|
|
-- -----------------------------------------------------------------------------
|
|
-- The Flask app uses pctype table but expects certain columns
|
|
ALTER TABLE pctype
|
|
ADD COLUMN IF NOT EXISTS createddate DATETIME DEFAULT NOW(),
|
|
ADD COLUMN IF NOT EXISTS modifieddate DATETIME DEFAULT NOW();
|
|
|
|
-- Ensure isactive is correct type
|
|
ALTER TABLE pctype MODIFY COLUMN isactive TINYINT(1) DEFAULT 1;
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- statuses table (if needed - Flask uses assetstatuses)
|
|
-- -----------------------------------------------------------------------------
|
|
-- assetstatuses already populated earlier
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- subnets table
|
|
-- -----------------------------------------------------------------------------
|
|
ALTER TABLE subnets
|
|
ADD COLUMN IF NOT EXISTS createddate DATETIME DEFAULT NOW(),
|
|
ADD COLUMN IF NOT EXISTS modifieddate DATETIME DEFAULT NOW(),
|
|
ADD COLUMN IF NOT EXISTS isactive TINYINT(1) DEFAULT 1;
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- vlans table
|
|
-- -----------------------------------------------------------------------------
|
|
ALTER TABLE vlans
|
|
ADD COLUMN IF NOT EXISTS createddate DATETIME DEFAULT NOW(),
|
|
ADD COLUMN IF NOT EXISTS modifieddate DATETIME DEFAULT NOW(),
|
|
ADD COLUMN IF NOT EXISTS isactive TINYINT(1) DEFAULT 1;
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- usbdevices table
|
|
-- -----------------------------------------------------------------------------
|
|
ALTER TABLE usbdevices
|
|
ADD COLUMN IF NOT EXISTS createddate DATETIME DEFAULT NOW(),
|
|
ADD COLUMN IF NOT EXISTS modifieddate DATETIME DEFAULT NOW(),
|
|
ADD COLUMN IF NOT EXISTS isactive TINYINT(1) DEFAULT 1;
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- usbcheckouts table
|
|
-- -----------------------------------------------------------------------------
|
|
ALTER TABLE usbcheckouts
|
|
ADD COLUMN IF NOT EXISTS createddate DATETIME DEFAULT NOW(),
|
|
ADD COLUMN IF NOT EXISTS modifieddate DATETIME DEFAULT NOW();
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- notifications table
|
|
-- -----------------------------------------------------------------------------
|
|
ALTER TABLE notifications
|
|
ADD COLUMN IF NOT EXISTS createddate DATETIME DEFAULT NOW(),
|
|
ADD COLUMN IF NOT EXISTS modifieddate DATETIME DEFAULT NOW();
|
|
|
|
-- Copy dates from existing columns if they exist
|
|
UPDATE notifications SET createddate = startdate WHERE createddate IS NULL;
|
|
UPDATE notifications SET modifieddate = startdate WHERE modifieddate IS NULL;
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- Verify key counts
|
|
-- -----------------------------------------------------------------------------
|
|
SELECT 'Migration complete. Record counts:' as status;
|
|
SELECT 'vendors' as tbl, COUNT(*) as cnt FROM vendors
|
|
UNION ALL SELECT 'models', COUNT(*) FROM models
|
|
UNION ALL SELECT 'machinetypes', COUNT(*) FROM machinetypes
|
|
UNION ALL SELECT 'operatingsystems', COUNT(*) FROM operatingsystems
|
|
UNION ALL SELECT 'businessunits', COUNT(*) FROM businessunits
|
|
UNION ALL SELECT 'applications', COUNT(*) FROM applications
|
|
UNION ALL SELECT 'machines', COUNT(*) FROM machines
|
|
UNION ALL SELECT 'printers', COUNT(*) FROM printers
|
|
UNION ALL SELECT 'assets', COUNT(*) FROM assets
|
|
UNION ALL SELECT 'knowledgebase', COUNT(*) FROM knowledgebase
|
|
UNION ALL SELECT 'notifications', COUNT(*) FROM notifications;
|