33 lines
1.2 KiB
SQL
33 lines
1.2 KiB
SQL
SET @equipment_area_column_exists := (
|
||
SELECT COUNT(*)
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'em_equipment'
|
||
AND COLUMN_NAME = 'occupied_area_sqm'
|
||
);
|
||
|
||
SET @add_equipment_area_column_sql := IF(
|
||
@equipment_area_column_exists = 0,
|
||
'ALTER TABLE em_equipment ADD COLUMN occupied_area_sqm DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT ''占用场地面积(平方米)'' AFTER rated_power_kw',
|
||
'SELECT ''em_equipment.occupied_area_sqm already exists'''
|
||
);
|
||
|
||
PREPARE add_equipment_area_column_stmt FROM @add_equipment_area_column_sql;
|
||
EXECUTE add_equipment_area_column_stmt;
|
||
DEALLOCATE PREPARE add_equipment_area_column_stmt;
|
||
|
||
ALTER TABLE fi_overhead_entry
|
||
MODIFY COLUMN allocation_basis VARCHAR(32) NOT NULL DEFAULT 'WORK_HOURS' COMMENT '分摊依据：WORK_HOURS/MACHINE_HOURS/QTY/OCCUPIED_AREA';
|
||
|
||
UPDATE em_equipment
|
||
SET occupied_area_sqm = CASE equipment_code
|
||
WHEN 'EQ-KP-01' THEN 12.50
|
||
WHEN 'EQ-CY-01' THEN 16.00
|
||
WHEN 'EQ-MC-01' THEN 8.00
|
||
WHEN 'EQ-ZW-01' THEN 14.00
|
||
WHEN 'EQ-HJ-01' THEN 6.00
|
||
WHEN 'EQ-QA-01' THEN 4.00
|
||
ELSE occupied_area_sqm
|
||
END
|
||
WHERE equipment_code IN ('EQ-KP-01', 'EQ-CY-01', 'EQ-MC-01', 'EQ-ZW-01', 'EQ-HJ-01', 'EQ-QA-01');
|