9b79c14267
- 新增 SoftwareCategory/SoftwareAsset/SoftwareChangeLog 模型与迁移0010 - 软资产CRUD:编号/名称/分类/供应商/版本/许可类型/授权数量/许可密钥/账号/费用/起止日期/负责人/部门/状态 - 许可类型:永久授权/订阅/开源免费/按需付费;状态:在用/闲置/已过期/已注销 - 到期提醒:已过期/即将到期(30天)自动标识 - Excel导入导出:模板下载、ID存在时更新、分类自动创建、空白行跳过、缺编号/名称报错 - 软件分类独立管理,分类下有资产时禁止删除 - 独立软件变更记录页;仪表盘增加软件统计卡片、软件分类统计、许可类型分布 - 导航栏改造:资产管理/分类管理/变更记录/导入导出下拉菜单 - 全流程15项功能验证通过(CRUD/导入导出/权限/仪表盘)
517 lines
20 KiB
Python
517 lines
20 KiB
Python
"""Excel导入导出工具"""
|
|
from openpyxl import Workbook
|
|
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
|
|
from openpyxl.utils import get_column_letter
|
|
from django.utils import timezone
|
|
from .models import Category
|
|
|
|
|
|
# Excel列定义(顺序与资产列表一致)
|
|
EXPORT_COLUMNS = [
|
|
('id', 'ID', 8),
|
|
('location', '机房', 20),
|
|
('cabinet', '机柜', 10),
|
|
('cabinet_position', '机柜位置', 10),
|
|
('asset_number', '资产编号', 18),
|
|
('name', '设备名称', 20),
|
|
('category', '设备分类', 12),
|
|
('brand', '品牌', 12),
|
|
('model', '型号', 20),
|
|
('asset_value', '资产面值', 12),
|
|
('serial_number', '序列号', 25),
|
|
('bmc_address', 'BMC地址', 16),
|
|
('ip_address', 'IP地址', 16),
|
|
('gpu_type', '显卡类型', 15),
|
|
('gpu_count', '卡数', 6),
|
|
('responsible_person', '负责人', 10),
|
|
('department', '使用部门', 15),
|
|
('user', '维护人', 10),
|
|
('business_type', '业务类型', 15),
|
|
('status', '状态', 8),
|
|
('purchase_date', '采购日期', 12),
|
|
('warranty_expire', '质保到期', 12),
|
|
('supplier', '供应商', 15),
|
|
('remark', '备注', 30),
|
|
]
|
|
|
|
STATUS_MAP = {
|
|
'in_use': '在用', 'idle': '闲置', 'maintenance': '维修中', 'scrapped': '已报废',
|
|
}
|
|
STATUS_MAP_REVERSE = {v: k for k, v in STATUS_MAP.items()}
|
|
|
|
# 深色主题样式
|
|
HEADER_FILL = PatternFill(start_color='1B2838', end_color='1B2838', fill_type='solid')
|
|
HEADER_FONT = Font(name='微软雅黑', bold=True, color='FFFFFF', size=11)
|
|
CELL_FONT = Font(name='微软雅黑', size=10)
|
|
THIN_BORDER = Border(
|
|
left=Side(style='thin', color='3A4A5C'),
|
|
right=Side(style='thin', color='3A4A5C'),
|
|
top=Side(style='thin', color='3A4A5C'),
|
|
bottom=Side(style='thin', color='3A4A5C'),
|
|
)
|
|
|
|
|
|
def export_assets_to_excel(queryset):
|
|
"""导出资产到Excel"""
|
|
wb = Workbook()
|
|
ws = wb.active
|
|
ws.title = '硬件资产'
|
|
|
|
# 写表头
|
|
for col_idx, (field, header, width) in enumerate(EXPORT_COLUMNS, 1):
|
|
cell = ws.cell(row=1, column=col_idx, value=header)
|
|
cell.fill = HEADER_FILL
|
|
cell.font = HEADER_FONT
|
|
cell.alignment = Alignment(horizontal='center', vertical='center')
|
|
cell.border = THIN_BORDER
|
|
ws.column_dimensions[get_column_letter(col_idx)].width = width
|
|
|
|
# 写数据
|
|
for row_idx, asset in enumerate(queryset, 2):
|
|
for col_idx, (field, _, _) in enumerate(EXPORT_COLUMNS, 1):
|
|
if field == 'id':
|
|
value = asset.id
|
|
elif field == 'category':
|
|
value = str(asset.category) if asset.category else ''
|
|
elif field == 'status':
|
|
value = STATUS_MAP.get(asset.status, asset.status)
|
|
elif field in ('purchase_date', 'warranty_expire'):
|
|
value = str(getattr(asset, field, '')) or ''
|
|
else:
|
|
value = getattr(asset, field, '') or ''
|
|
|
|
cell = ws.cell(row=row_idx, column=col_idx, value=value)
|
|
cell.font = CELL_FONT
|
|
cell.border = THIN_BORDER
|
|
cell.alignment = Alignment(vertical='center')
|
|
|
|
# 冻结首行
|
|
ws.freeze_panes = 'A2'
|
|
|
|
return wb
|
|
|
|
|
|
def generate_import_template():
|
|
"""生成导入模板"""
|
|
wb = Workbook()
|
|
ws = wb.active
|
|
ws.title = '资产导入模板'
|
|
|
|
headers = [header for _, header, width in EXPORT_COLUMNS]
|
|
for col_idx, (field, header, width) in enumerate(EXPORT_COLUMNS, 1):
|
|
cell = ws.cell(row=1, column=col_idx, value=header)
|
|
cell.fill = HEADER_FILL
|
|
cell.font = HEADER_FONT
|
|
cell.alignment = Alignment(horizontal='center', vertical='center')
|
|
cell.border = THIN_BORDER
|
|
ws.column_dimensions[get_column_letter(col_idx)].width = width
|
|
|
|
# 示例数据行(顺序与EXPORT_COLUMNS一致)
|
|
example_data = [
|
|
'1', '3楼机房A区', 'A01', 'U10-U15', 'IT-2024-0001', '测试服务器',
|
|
'服务器', 'Dell', 'PowerEdge R740', '50000.00', 'ABC123456',
|
|
'192.168.1.200', '192.168.1.100', 'NVIDIA A100', '8', '张三',
|
|
'研发部', '李四', 'AI训练', '在用', '2024-01-15', '2027-01-15',
|
|
'戴尔科技', '测试备注'
|
|
]
|
|
for col_idx, value in enumerate(example_data, 1):
|
|
cell = ws.cell(row=2, column=col_idx, value=value)
|
|
cell.font = Font(name='微软雅黑', size=10, color='666666')
|
|
cell.border = THIN_BORDER
|
|
|
|
ws.freeze_panes = 'A2'
|
|
|
|
return wb
|
|
|
|
|
|
def import_assets_from_excel(ws, category_map, operator=None):
|
|
"""从Excel导入资产,返回结果统计"""
|
|
from .models import Asset, AssetChangeLog
|
|
|
|
header_row = [cell.value for cell in ws[1]]
|
|
field_map = {header: field for field, header, _ in EXPORT_COLUMNS}
|
|
|
|
results = {
|
|
'success': 0,
|
|
'skipped': 0,
|
|
'errors': [],
|
|
'total': 0,
|
|
}
|
|
|
|
for row_idx, row in enumerate(ws.iter_rows(min_row=2, values_only=True), 2):
|
|
if not row or not row[0]:
|
|
continue
|
|
|
|
results['total'] += 1
|
|
|
|
try:
|
|
data = {}
|
|
for col_idx, value in enumerate(row):
|
|
if col_idx < len(header_row):
|
|
field = field_map.get(header_row[col_idx])
|
|
if field:
|
|
data[field] = str(value).strip() if value else ''
|
|
|
|
# 资产编号必填
|
|
asset_number = data.get('asset_number', '').strip()
|
|
if not asset_number:
|
|
results['errors'].append(f'第{row_idx}行: 缺少资产编号')
|
|
continue
|
|
|
|
# 处理分类 - 不存在则自动创建
|
|
category_name = data.get('category', '').strip()
|
|
category = category_map.get(category_name)
|
|
if not category and category_name:
|
|
category = Category.objects.create(name=category_name)
|
|
category_map[category_name] = category
|
|
elif not category_name:
|
|
category = category_map.get('未分类')
|
|
if not category:
|
|
category = Category.objects.create(name='未分类')
|
|
category_map['未分类'] = category
|
|
|
|
# 处理状态
|
|
status = STATUS_MAP_REVERSE.get(data.get('status', '在用'), 'in_use')
|
|
|
|
# 处理日期 - 直接存字符串
|
|
purchase_date = data.get('purchase_date', '').strip() or ''
|
|
warranty_expire = data.get('warranty_expire', '').strip() or ''
|
|
|
|
# 处理IP
|
|
bmc_address = data.get('bmc_address', '').strip() or ''
|
|
ip_address = data.get('ip_address', '').strip() or ''
|
|
|
|
# 资产面值 - 直接存字符串
|
|
asset_value = data.get('asset_value', '').strip() or ''
|
|
|
|
# 处理ID - 如果提供了ID且已存在,则更新该记录
|
|
import_id = data.get('id', '').strip()
|
|
asset = None
|
|
is_update = False
|
|
if import_id and import_id.isdigit():
|
|
try:
|
|
id_val = int(import_id) if str(import_id).strip().isdigit() else None
|
|
if id_val:
|
|
asset = Asset.objects.get(id=id_val)
|
|
is_update = True
|
|
except (Asset.DoesNotExist, ValueError):
|
|
asset = None
|
|
|
|
if asset:
|
|
# 更新已有记录
|
|
asset.asset_number = asset_number
|
|
asset.name = data.get('name', '')
|
|
asset.category = category
|
|
asset.brand = data.get('brand', '')
|
|
asset.model = data.get('model', '')
|
|
asset.asset_value = asset_value
|
|
asset.serial_number = data.get('serial_number', '')
|
|
asset.location = data.get('location', '')
|
|
asset.cabinet = data.get('cabinet', '')
|
|
asset.cabinet_position = data.get('cabinet_position', '')
|
|
asset.bmc_address = bmc_address
|
|
asset.ip_address = ip_address
|
|
asset.gpu_type = data.get('gpu_type', '')
|
|
gpu_count_str = data.get('gpu_count', '').strip()
|
|
asset.gpu_count = gpu_count_str if gpu_count_str else ''
|
|
asset.purchase_date = purchase_date
|
|
asset.warranty_expire = warranty_expire
|
|
asset.supplier = data.get('supplier', '')
|
|
asset.responsible_person = data.get('responsible_person', '')
|
|
asset.department = data.get('department', '')
|
|
asset.user = data.get('user', '')
|
|
asset.business_type = data.get('business_type', '')
|
|
asset.status = status
|
|
asset.remark = data.get('remark', '')
|
|
asset.save()
|
|
|
|
AssetChangeLog.objects.create(
|
|
asset=asset,
|
|
asset_number=asset.asset_number,
|
|
action='import',
|
|
description=f'通过Excel导入更新(ID:{asset.id})',
|
|
operator=operator,
|
|
)
|
|
else:
|
|
# 创建新记录
|
|
gpu_count_str = data.get('gpu_count', '').strip()
|
|
gpu_count = gpu_count_str if gpu_count_str else ''
|
|
|
|
# 创建参数
|
|
create_kwargs = {
|
|
'asset_number': asset_number,
|
|
'name': data.get('name', ''),
|
|
'category': category,
|
|
'brand': data.get('brand', ''),
|
|
'model': data.get('model', ''),
|
|
'asset_value': asset_value,
|
|
'serial_number': data.get('serial_number', ''),
|
|
'location': data.get('location', ''),
|
|
'cabinet': data.get('cabinet', ''),
|
|
'cabinet_position': data.get('cabinet_position', ''),
|
|
'bmc_address': bmc_address,
|
|
'ip_address': ip_address,
|
|
'gpu_type': data.get('gpu_type', ''),
|
|
'gpu_count': gpu_count,
|
|
'purchase_date': purchase_date,
|
|
'warranty_expire': warranty_expire,
|
|
'supplier': data.get('supplier', ''),
|
|
'responsible_person': data.get('responsible_person', ''),
|
|
'department': data.get('department', ''),
|
|
'user': data.get('user', ''),
|
|
'business_type': data.get('business_type', ''),
|
|
'status': status,
|
|
'remark': data.get('remark', ''),
|
|
'created_by': operator,
|
|
}
|
|
|
|
# 如果Excel提供了ID,使用该ID创建
|
|
if import_id and import_id.isdigit():
|
|
try:
|
|
if str(import_id).strip().isdigit():
|
|
create_kwargs['id'] = int(import_id)
|
|
except ValueError:
|
|
pass
|
|
|
|
asset = Asset.objects.create(**create_kwargs)
|
|
|
|
AssetChangeLog.objects.create(
|
|
asset=asset,
|
|
asset_number=asset.asset_number,
|
|
action='import',
|
|
description=f'通过Excel导入创建',
|
|
operator=operator,
|
|
)
|
|
|
|
results['success'] += 1
|
|
|
|
except Exception as e:
|
|
results['errors'].append(f'第{row_idx}行: {str(e)}')
|
|
|
|
return results
|
|
|
|
|
|
# ─── 软资产(软件资产)Excel 支持 ─────────────────
|
|
|
|
# Excel列定义(顺序与软件资产列表一致)
|
|
SOFTWARE_EXPORT_COLUMNS = [
|
|
('id', 'ID', 8),
|
|
('asset_number', '资产编号', 18),
|
|
('name', '软件名称', 25),
|
|
('category', '软件分类', 12),
|
|
('vendor', '供应商', 18),
|
|
('version', '版本', 12),
|
|
('license_type', '许可类型', 12),
|
|
('seats', '授权数量', 10),
|
|
('license_key', '许可密钥/激活码', 30),
|
|
('account', '账号', 18),
|
|
('cost', '费用(元)', 12),
|
|
('start_date', '启用日期', 12),
|
|
('expire_date', '到期日期', 12),
|
|
('responsible_person', '负责人', 10),
|
|
('department', '使用部门', 15),
|
|
('status', '状态', 8),
|
|
('remark', '备注', 30),
|
|
]
|
|
|
|
SOFTWARE_LICENSE_TYPE_MAP = {
|
|
'permanent': '永久授权', 'subscription': '订阅',
|
|
'open_source': '开源免费', 'pay_as_you_go': '按需付费',
|
|
}
|
|
SOFTWARE_LICENSE_TYPE_REVERSE = {v: k for k, v in SOFTWARE_LICENSE_TYPE_MAP.items()}
|
|
|
|
SOFTWARE_STATUS_MAP = {
|
|
'in_use': '在用', 'idle': '闲置', 'expired': '已过期', 'cancelled': '已注销',
|
|
}
|
|
SOFTWARE_STATUS_MAP_REVERSE = {v: k for k, v in SOFTWARE_STATUS_MAP.items()}
|
|
|
|
|
|
def export_software_assets_to_excel(queryset):
|
|
"""导出软件资产到Excel"""
|
|
wb = Workbook()
|
|
ws = wb.active
|
|
ws.title = '软件资产'
|
|
|
|
for col_idx, (field, header, width) in enumerate(SOFTWARE_EXPORT_COLUMNS, 1):
|
|
cell = ws.cell(row=1, column=col_idx, value=header)
|
|
cell.fill = HEADER_FILL
|
|
cell.font = HEADER_FONT
|
|
cell.alignment = Alignment(horizontal='center', vertical='center')
|
|
cell.border = THIN_BORDER
|
|
ws.column_dimensions[get_column_letter(col_idx)].width = width
|
|
|
|
for row_idx, sw in enumerate(queryset, 2):
|
|
for col_idx, (field, _, _) in enumerate(SOFTWARE_EXPORT_COLUMNS, 1):
|
|
if field == 'id':
|
|
value = sw.id
|
|
elif field == 'category':
|
|
value = str(sw.category) if sw.category else ''
|
|
elif field == 'status':
|
|
value = SOFTWARE_STATUS_MAP.get(sw.status, sw.status)
|
|
elif field == 'license_type':
|
|
value = SOFTWARE_LICENSE_TYPE_MAP.get(sw.license_type, sw.license_type)
|
|
else:
|
|
value = getattr(sw, field, '') or ''
|
|
|
|
cell = ws.cell(row=row_idx, column=col_idx, value=value)
|
|
cell.font = CELL_FONT
|
|
cell.border = THIN_BORDER
|
|
cell.alignment = Alignment(vertical='center')
|
|
|
|
ws.freeze_panes = 'A2'
|
|
return wb
|
|
|
|
|
|
def generate_software_import_template():
|
|
"""生成软件资产导入模板"""
|
|
wb = Workbook()
|
|
ws = wb.active
|
|
ws.title = '软件资产导入模板'
|
|
|
|
for col_idx, (field, header, width) in enumerate(SOFTWARE_EXPORT_COLUMNS, 1):
|
|
cell = ws.cell(row=1, column=col_idx, value=header)
|
|
cell.fill = HEADER_FILL
|
|
cell.font = HEADER_FONT
|
|
cell.alignment = Alignment(horizontal='center', vertical='center')
|
|
cell.border = THIN_BORDER
|
|
ws.column_dimensions[get_column_letter(col_idx)].width = width
|
|
|
|
example_data = [
|
|
'1', 'SW-2026-0001', 'Windows Server 2022', '操作系统', '微软', '2022 Datacenter',
|
|
'永久授权', '50', 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX', 'it@company.com',
|
|
'120000', '2025-01-01', '2027-01-01', '张三', '研发部', '在用', '采购备注'
|
|
]
|
|
for col_idx, value in enumerate(example_data, 1):
|
|
cell = ws.cell(row=2, column=col_idx, value=value)
|
|
cell.font = Font(name='微软雅黑', size=10, color='666666')
|
|
cell.border = THIN_BORDER
|
|
|
|
ws.freeze_panes = 'A2'
|
|
return wb
|
|
|
|
|
|
def import_software_from_excel(ws, category_map, operator=None):
|
|
"""从Excel导入软件资产,返回结果统计"""
|
|
from .models import SoftwareAsset, SoftwareCategory, SoftwareChangeLog
|
|
|
|
header_row = [cell.value for cell in ws[1]]
|
|
field_map = {header: field for field, header, _ in SOFTWARE_EXPORT_COLUMNS}
|
|
|
|
results = {
|
|
'success': 0,
|
|
'skipped': 0,
|
|
'errors': [],
|
|
'total': 0,
|
|
}
|
|
|
|
for row_idx, row in enumerate(ws.iter_rows(min_row=2, values_only=True), 2):
|
|
# 跳过完全空白的行(ID 列可以为空)
|
|
if not row or all(v is None or str(v).strip() == '' for v in row):
|
|
continue
|
|
|
|
results['total'] += 1
|
|
|
|
try:
|
|
data = {}
|
|
for col_idx, value in enumerate(row):
|
|
if col_idx < len(header_row):
|
|
field = field_map.get(header_row[col_idx])
|
|
if field:
|
|
data[field] = str(value).strip() if value is not None else ''
|
|
|
|
# 资产编号必填
|
|
asset_number = data.get('asset_number', '').strip()
|
|
if not asset_number:
|
|
results['errors'].append(f'第{row_idx}行: 缺少资产编号')
|
|
continue
|
|
|
|
# 软件名称必填
|
|
sw_name = data.get('name', '').strip()
|
|
if not sw_name:
|
|
results['errors'].append(f'第{row_idx}行: 缺少软件名称')
|
|
continue
|
|
|
|
# 处理分类 - 不存在则自动创建
|
|
category_name = data.get('category', '').strip()
|
|
category = category_map.get(category_name)
|
|
if not category and category_name:
|
|
category = SoftwareCategory.objects.create(name=category_name)
|
|
category_map[category_name] = category
|
|
elif not category_name:
|
|
category = category_map.get('未分类')
|
|
if not category:
|
|
category = SoftwareCategory.objects.create(name='未分类')
|
|
category_map['未分类'] = category
|
|
|
|
# 许可类型
|
|
license_type = SOFTWARE_LICENSE_TYPE_REVERSE.get(
|
|
data.get('license_type', '订阅'), 'subscription')
|
|
|
|
# 状态
|
|
status = SOFTWARE_STATUS_MAP_REVERSE.get(data.get('status', '在用'), 'in_use')
|
|
|
|
# 处理ID - 如果提供了ID且已存在,则更新该记录
|
|
import_id = data.get('id', '').strip()
|
|
sw_asset = None
|
|
if import_id and import_id.isdigit():
|
|
try:
|
|
sw_asset = SoftwareAsset.objects.get(id=int(import_id))
|
|
except (SoftwareAsset.DoesNotExist, ValueError):
|
|
sw_asset = None
|
|
|
|
common_fields = {
|
|
'asset_number': asset_number,
|
|
'name': sw_name,
|
|
'category': category,
|
|
'vendor': data.get('vendor', ''),
|
|
'version': data.get('version', ''),
|
|
'license_type': license_type,
|
|
'license_key': data.get('license_key', ''),
|
|
'seats': data.get('seats', ''),
|
|
'account': data.get('account', ''),
|
|
'cost': data.get('cost', ''),
|
|
'start_date': data.get('start_date', '').strip(),
|
|
'expire_date': data.get('expire_date', '').strip(),
|
|
'responsible_person': data.get('responsible_person', ''),
|
|
'department': data.get('department', ''),
|
|
'status': status,
|
|
'remark': data.get('remark', ''),
|
|
}
|
|
|
|
if sw_asset:
|
|
# 更新已有记录
|
|
for field, value in common_fields.items():
|
|
setattr(sw_asset, field, value)
|
|
sw_asset.save()
|
|
|
|
SoftwareChangeLog.objects.create(
|
|
software_asset=sw_asset,
|
|
asset_number=sw_asset.asset_number,
|
|
action='import',
|
|
description=f'通过Excel导入更新(ID:{sw_asset.id})',
|
|
operator=operator,
|
|
)
|
|
else:
|
|
# 创建新记录
|
|
create_kwargs = dict(common_fields)
|
|
create_kwargs['created_by'] = operator
|
|
if import_id and import_id.isdigit():
|
|
create_kwargs['id'] = int(import_id)
|
|
|
|
sw_asset = SoftwareAsset.objects.create(**create_kwargs)
|
|
|
|
SoftwareChangeLog.objects.create(
|
|
software_asset=sw_asset,
|
|
asset_number=sw_asset.asset_number,
|
|
action='import',
|
|
description='通过Excel导入创建',
|
|
operator=operator,
|
|
)
|
|
|
|
results['success'] += 1
|
|
|
|
except Exception as e:
|
|
results['errors'].append(f'第{row_idx}行: {str(e)}')
|
|
|
|
return results
|