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>
286 lines
10 KiB
Python
286 lines
10 KiB
Python
"""
|
|
Migrate machines table to assets + extension tables.
|
|
|
|
This script migrates data from the legacy machines table to the new
|
|
Asset architecture with plugin-owned extension tables.
|
|
|
|
Strategy:
|
|
1. Preserve IDs: assets.assetid = original machines.machineid
|
|
2. Create asset record, then type-specific extension record
|
|
3. Map machine types to asset types:
|
|
- MachineType = Equipment -> equipment extension
|
|
- MachineType = PC -> computers extension
|
|
- MachineType = Network/Camera/etc -> network_devices extension
|
|
- Printers -> handled separately by printers plugin
|
|
|
|
Usage:
|
|
python -m scripts.migration.migrate_assets --source <connection_string>
|
|
"""
|
|
|
|
import argparse
|
|
import logging
|
|
from datetime import datetime
|
|
from sqlalchemy import create_engine, text
|
|
from sqlalchemy.orm import sessionmaker
|
|
|
|
logging.basicConfig(level=logging.INFO)
|
|
logger = logging.getLogger(__name__)
|
|
|
|
|
|
def get_machine_type_mapping():
|
|
"""Map legacy machine type IDs to asset types."""
|
|
return {
|
|
# Equipment types
|
|
'CNC': 'equipment',
|
|
'CMM': 'equipment',
|
|
'Lathe': 'equipment',
|
|
'Grinder': 'equipment',
|
|
'EDM': 'equipment',
|
|
'Mill': 'equipment',
|
|
'Press': 'equipment',
|
|
'Robot': 'equipment',
|
|
'Part Marker': 'equipment',
|
|
# PC types
|
|
'PC': 'computer',
|
|
'Workstation': 'computer',
|
|
'Laptop': 'computer',
|
|
'Server': 'computer',
|
|
# Network types
|
|
'Switch': 'network_device',
|
|
'Router': 'network_device',
|
|
'Access Point': 'network_device',
|
|
'Camera': 'network_device',
|
|
'IDF': 'network_device',
|
|
'MDF': 'network_device',
|
|
'Firewall': 'network_device',
|
|
}
|
|
|
|
|
|
def migrate_machine_to_asset(machine_row, asset_type_id, target_session):
|
|
"""
|
|
Create an Asset record from a Machine record.
|
|
|
|
Args:
|
|
machine_row: Row from source machines table
|
|
asset_type_id: Target asset type ID
|
|
target_session: SQLAlchemy session for target database
|
|
|
|
Returns:
|
|
Created asset ID
|
|
"""
|
|
# Insert into assets table
|
|
target_session.execute(text("""
|
|
INSERT INTO assets (
|
|
assetid, assetnumber, name, serialnumber,
|
|
assettypeid, statusid, locationid, businessunitid,
|
|
mapleft, maptop, notes, isactive, createddate, modifieddate
|
|
) VALUES (
|
|
:assetid, :assetnumber, :name, :serialnumber,
|
|
:assettypeid, :statusid, :locationid, :businessunitid,
|
|
:mapleft, :maptop, :notes, :isactive, :createddate, :modifieddate
|
|
)
|
|
"""), {
|
|
'assetid': machine_row['machineid'],
|
|
'assetnumber': machine_row['machinenumber'],
|
|
'name': machine_row.get('alias'),
|
|
'serialnumber': machine_row.get('serialnumber'),
|
|
'assettypeid': asset_type_id,
|
|
'statusid': machine_row.get('statusid', 1),
|
|
'locationid': machine_row.get('locationid'),
|
|
'businessunitid': machine_row.get('businessunitid'),
|
|
'mapleft': machine_row.get('mapleft'),
|
|
'maptop': machine_row.get('maptop'),
|
|
'notes': machine_row.get('notes'),
|
|
'isactive': machine_row.get('isactive', True),
|
|
'createddate': machine_row.get('createddate', datetime.utcnow()),
|
|
'modifieddate': machine_row.get('modifieddate', datetime.utcnow()),
|
|
})
|
|
|
|
return machine_row['machineid']
|
|
|
|
|
|
def migrate_equipment(machine_row, asset_id, target_session):
|
|
"""Create equipment extension record."""
|
|
target_session.execute(text("""
|
|
INSERT INTO equipment (
|
|
assetid, equipmenttypeid, vendorid, modelnumberid,
|
|
requiresmanualconfig, islocationonly, isactive, createddate
|
|
) VALUES (
|
|
:assetid, :equipmenttypeid, :vendorid, :modelnumberid,
|
|
:requiresmanualconfig, :islocationonly, :isactive, :createddate
|
|
)
|
|
"""), {
|
|
'assetid': asset_id,
|
|
'equipmenttypeid': machine_row.get('machinetypeid'), # May need mapping
|
|
'vendorid': machine_row.get('vendorid'),
|
|
'modelnumberid': machine_row.get('modelnumberid'),
|
|
'requiresmanualconfig': machine_row.get('requiresmanualconfig', False),
|
|
'islocationonly': machine_row.get('islocationonly', False),
|
|
'isactive': True,
|
|
'createddate': datetime.utcnow(),
|
|
})
|
|
|
|
|
|
def migrate_computer(machine_row, asset_id, target_session):
|
|
"""Create computer extension record."""
|
|
target_session.execute(text("""
|
|
INSERT INTO computers (
|
|
assetid, computertypeid, vendorid, operatingsystemid,
|
|
hostname, currentuserid, lastuserid, lastboottime,
|
|
lastzabbixsync, isvnc, isactive, createddate
|
|
) VALUES (
|
|
:assetid, :computertypeid, :vendorid, :operatingsystemid,
|
|
:hostname, :currentuserid, :lastuserid, :lastboottime,
|
|
:lastzabbixsync, :isvnc, :isactive, :createddate
|
|
)
|
|
"""), {
|
|
'assetid': asset_id,
|
|
'computertypeid': machine_row.get('pctypeid'),
|
|
'vendorid': machine_row.get('vendorid'),
|
|
'operatingsystemid': machine_row.get('operatingsystemid'),
|
|
'hostname': machine_row.get('hostname'),
|
|
'currentuserid': machine_row.get('currentuserid'),
|
|
'lastuserid': machine_row.get('lastuserid'),
|
|
'lastboottime': machine_row.get('lastboottime'),
|
|
'lastzabbixsync': machine_row.get('lastzabbixsync'),
|
|
'isvnc': machine_row.get('isvnc', False),
|
|
'isactive': True,
|
|
'createddate': datetime.utcnow(),
|
|
})
|
|
|
|
|
|
def migrate_network_device(machine_row, asset_id, target_session):
|
|
"""Create network device extension record."""
|
|
target_session.execute(text("""
|
|
INSERT INTO networkdevices (
|
|
assetid, networkdevicetypeid, vendorid, hostname,
|
|
firmwareversion, portcount, ispoe, ismanaged,
|
|
isactive, createddate
|
|
) VALUES (
|
|
:assetid, :networkdevicetypeid, :vendorid, :hostname,
|
|
:firmwareversion, :portcount, :ispoe, :ismanaged,
|
|
:isactive, :createddate
|
|
)
|
|
"""), {
|
|
'assetid': asset_id,
|
|
'networkdevicetypeid': machine_row.get('machinetypeid'), # May need mapping
|
|
'vendorid': machine_row.get('vendorid'),
|
|
'hostname': machine_row.get('hostname'),
|
|
'firmwareversion': machine_row.get('firmwareversion'),
|
|
'portcount': machine_row.get('portcount'),
|
|
'ispoe': machine_row.get('ispoe', False),
|
|
'ismanaged': machine_row.get('ismanaged', False),
|
|
'isactive': True,
|
|
'createddate': datetime.utcnow(),
|
|
})
|
|
|
|
|
|
def run_migration(source_conn_str, target_conn_str, dry_run=False):
|
|
"""
|
|
Run the full migration from machines to assets.
|
|
|
|
Args:
|
|
source_conn_str: Connection string for source (VBScript) database
|
|
target_conn_str: Connection string for target (Flask) database
|
|
dry_run: If True, don't commit changes
|
|
"""
|
|
source_engine = create_engine(source_conn_str)
|
|
target_engine = create_engine(target_conn_str)
|
|
|
|
SourceSession = sessionmaker(bind=source_engine)
|
|
TargetSession = sessionmaker(bind=target_engine)
|
|
|
|
source_session = SourceSession()
|
|
target_session = TargetSession()
|
|
|
|
try:
|
|
# Get asset type mappings from target database
|
|
asset_types = {}
|
|
result = target_session.execute(text("SELECT assettypeid, assettype FROM assettypes"))
|
|
for row in result:
|
|
asset_types[row.assettype] = row.assettypeid
|
|
|
|
# Get machine type to asset type mapping
|
|
type_mapping = get_machine_type_mapping()
|
|
|
|
# Fetch all machines from source
|
|
machines = source_session.execute(text("""
|
|
SELECT m.*, mt.machinetype
|
|
FROM machines m
|
|
LEFT JOIN machinetypes mt ON m.machinetypeid = mt.machinetypeid
|
|
"""))
|
|
|
|
migrated = 0
|
|
errors = 0
|
|
|
|
for machine in machines:
|
|
machine_dict = dict(machine._mapping)
|
|
|
|
try:
|
|
# Determine asset type
|
|
machine_type_name = machine_dict.get('machinetype', '')
|
|
asset_type_name = type_mapping.get(machine_type_name, 'equipment')
|
|
asset_type_id = asset_types.get(asset_type_name)
|
|
|
|
if not asset_type_id:
|
|
logger.warning(f"Unknown asset type for machine {machine_dict['machineid']}: {machine_type_name}")
|
|
errors += 1
|
|
continue
|
|
|
|
# Create asset record
|
|
asset_id = migrate_machine_to_asset(machine_dict, asset_type_id, target_session)
|
|
|
|
# Create extension record based on type
|
|
if asset_type_name == 'equipment':
|
|
migrate_equipment(machine_dict, asset_id, target_session)
|
|
elif asset_type_name == 'computer':
|
|
migrate_computer(machine_dict, asset_id, target_session)
|
|
elif asset_type_name == 'network_device':
|
|
migrate_network_device(machine_dict, asset_id, target_session)
|
|
|
|
migrated += 1
|
|
|
|
if migrated % 100 == 0:
|
|
logger.info(f"Migrated {migrated} machines...")
|
|
|
|
except Exception as e:
|
|
logger.error(f"Error migrating machine {machine_dict.get('machineid')}: {e}")
|
|
errors += 1
|
|
|
|
if dry_run:
|
|
logger.info("Dry run - rolling back changes")
|
|
target_session.rollback()
|
|
else:
|
|
target_session.commit()
|
|
|
|
logger.info(f"Migration complete: {migrated} migrated, {errors} errors")
|
|
|
|
finally:
|
|
source_session.close()
|
|
target_session.close()
|
|
|
|
|
|
def main():
|
|
parser = argparse.ArgumentParser(description='Migrate machines to assets')
|
|
parser.add_argument('--source', required=True, help='Source database connection string')
|
|
parser.add_argument('--target', help='Target database connection string (default: app config)')
|
|
parser.add_argument('--dry-run', action='store_true', help='Dry run without committing')
|
|
|
|
args = parser.parse_args()
|
|
|
|
target = args.target
|
|
if not target:
|
|
# Load from Flask config
|
|
import os
|
|
import sys
|
|
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.dirname(__file__))))
|
|
from shopdb import create_app
|
|
app = create_app()
|
|
target = app.config['SQLALCHEMY_DATABASE_URI']
|
|
|
|
run_migration(args.source, target, args.dry_run)
|
|
|
|
|
|
if __name__ == '__main__':
|
|
main()
|