app.py - master - otk11neter/TestMESSystem

app.py
587 строк23 KB
otk11neter
BatareonV2.0
30 янв 2026, 15:07
30 янв 2026, 15:070194816
from flask import Flask, request, jsonify, send_from_directory
from flask_cors import CORS
import sqlite3
import json
from datetime import datetime
import os
import tempfile
from openpyxl import load_workbook
import mimetypes
import ifcopenshell

app = Flask(__name__, static_folder='.', static_url_path='')
CORS(app, resources={r"/api/*": {"origins": "*"}})

# Регистрируем MIME типы
mimetypes.add_type('text/css', '.css')
mimetypes.add_type('application/javascript', '.js')

DB_FILE = 'kanban.db'

def get_db():
    """Получить подключение к базе данных"""
    conn = sqlite3.connect(DB_FILE)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    """Инициализировать базу данных с индексами"""
    conn = get_db()
    cursor = conn.cursor()
    
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS batches (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            status TEXT NOT NULL,
            groups TEXT NOT NULL,
            scrap INTEGER DEFAULT 0,
            archived INTEGER DEFAULT 0,
            created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    try:
        cursor.execute('ALTER TABLE batches ADD COLUMN archived INTEGER DEFAULT 0')
    except sqlite3.OperationalError:
        pass
    
    try:
        cursor.execute('ALTER TABLE batches ADD COLUMN masterStations TEXT DEFAULT \'{}\'')
    except sqlite3.OperationalError:
        pass
    
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_status ON batches(status)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_archived ON batches(archived)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_created ON batches(created DESC)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_title ON batches(title)')
    
    conn.commit()
    conn.close()
    print(f"✅ База данных '{DB_FILE}' инициализирована")

init_db()

def validate_batch_data(data):
    """Валидировать данные партии"""
    errors = []
    
    if not data.get('title') or not isinstance(data.get('title'), str):
        errors.append('title: Название должно быть строкой')
    elif len(data.get('title', '').strip()) == 0:
        errors.append('title: Название не может быть пусто')
    elif len(data.get('title', '')) > 200:
        errors.append('title: Название слишком длинное (макс 200 символов)')
    
    if data.get('status') not in ['production', 'todo', 'progress', 'done']:
        errors.append('status: Неверный статус')
    
    if not isinstance(data.get('groups'), list):
        errors.append('groups: Groups должен быть массивом')
    
    try:
        scrap = int(data.get('scrap', 0))
        if scrap < 0:
            errors.append('scrap: Брак не может быть отрицательным')
    except:
        errors.append('scrap: Брак должен быть числом')
    
    return errors

@app.route('/api/batches', methods=['GET'])
def get_batches():
    """Получить все партии"""
    try:
        page = int(request.args.get('page', 1))
        per_page = int(request.args.get('per_page', 50))
        status = request.args.get('status')
        
        if page < 1 or per_page < 1 or per_page > 100:
            return jsonify({'error': 'Неверные параметры пагинации'}), 400
        
        conn = get_db()
        cursor = conn.cursor()
        
        offset = (page - 1) * per_page
        
        if status:
            cursor.execute('SELECT * FROM batches WHERE status = ? ORDER BY created DESC LIMIT ? OFFSET ?', 
                         (status, per_page, offset))
        else:
            cursor.execute('SELECT * FROM batches ORDER BY created DESC LIMIT ? OFFSET ?', 
                         (per_page, offset))
        
        batches = []
        for row in cursor.fetchall():
            batch = dict(row)
            batch['groups'] = json.loads(batch['groups'])
            if batch.get('masterStations'):
                try:
                    batch['masterStations'] = json.loads(batch['masterStations'])
                except:
                    batch['masterStations'] = {}
            else:
                batch['masterStations'] = {}
            batches.append(batch)
        
        conn.close()
        return jsonify(batches)
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/batches/<int:batch_id>', methods=['GET'])
def get_batch(batch_id):
    """Получить партию по ID"""
    try:
        conn = get_db()
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM batches WHERE id = ?', (batch_id,))
        row = cursor.fetchone()
        conn.close()
        
        if not row:
            return jsonify({'error': 'Партия не найдена'}), 404
        
        batch = dict(row)
        batch['groups'] = json.loads(batch['groups'])
        if batch.get('masterStations'):
            try:
                batch['masterStations'] = json.loads(batch['masterStations'])
            except:
                batch['masterStations'] = {}
        else:
            batch['masterStations'] = {}
        return jsonify(batch)
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/batches', methods=['POST'])
def create_batch():
    """Создать новую партию"""
    try:
        data = request.get_json()
        
        errors = validate_batch_data(data)
        if errors:
            return jsonify({'error': 'Ошибка валидации', 'details': errors}), 400
        
        conn = get_db()
        cursor = conn.cursor()
        cursor.execute('''
            INSERT INTO batches (title, status, groups, scrap, masterStations)
            VALUES (?, ?, ?, ?, ?)
        ''', (
            data.get('title', '').strip(),
            data.get('status', 'todo'),
            json.dumps(data.get('groups', [{'count': 0} for _ in range(12)])),
            int(data.get('scrap', 0)),
            json.dumps(data.get('masterStations', {}))
        ))
        
        conn.commit()
        batch_id = cursor.lastrowid
        conn.close()
        
        return jsonify({'id': batch_id, 'status': 'created'}), 201
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/batches/<int:batch_id>', methods=['PUT'])
def update_batch(batch_id):
    """Обновить партию"""
    try:
        data = request.get_json()
        
        errors = validate_batch_data(data)
        if errors:
            return jsonify({'error': 'Ошибка валидации', 'details': errors}), 400
        
        conn = get_db()
        cursor = conn.cursor()
        
        cursor.execute('SELECT id FROM batches WHERE id = ?', (batch_id,))
        if not cursor.fetchone():
            conn.close()
            return jsonify({'error': 'Партия не найдена'}), 404
        
        groups = data.get('groups')
        if isinstance(groups, list):
            groups = json.dumps(groups)
        
        master_stations = data.get('masterStations', {})
        if isinstance(master_stations, dict):
            master_stations = json.dumps(master_stations)
        
        archived = data.get('archived', False)
        if isinstance(archived, bool):
            archived = 1 if archived else 0
        
        cursor.execute('''
            UPDATE batches 
            SET title = ?, status = ?, groups = ?, scrap = ?, archived = ?, masterStations = ?, updated = CURRENT_TIMESTAMP
            WHERE id = ?
        ''', (
            data.get('title', '').strip(),
            data.get('status'),
            groups,
            int(data.get('scrap', 0)),
            archived,
            master_stations,
            batch_id
        ))
        
        conn.commit()
        conn.close()
        
        return jsonify({'id': batch_id, 'status': 'updated'})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/batches/<int:batch_id>', methods=['DELETE'])
def delete_batch(batch_id):
    """Удалить партию"""
    try:
        conn = get_db()
        cursor = conn.cursor()
        
        cursor.execute('DELETE FROM batches WHERE id = ?', (batch_id,))
        conn.commit()
        
        if cursor.rowcount == 0:
            conn.close()
            return jsonify({'error': 'Партия не найдена'}), 404
        
        conn.close()
        return jsonify({'status': 'deleted'})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/batches/by-status/<status>', methods=['GET'])
def get_batches_by_status(status):
    """Получить партии по статусу"""
    try:
        if status not in ['production', 'todo', 'progress', 'done']:
            return jsonify({'error': 'Неверный статус'}), 400
        
        conn = get_db()
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM batches WHERE status = ? ORDER BY created DESC', (status,))
        
        batches = []
        for row in cursor.fetchall():
            batch = dict(row)
            batch['groups'] = json.loads(batch['groups'])
            if batch.get('masterStations'):
                try:
                    batch['masterStations'] = json.loads(batch['masterStations'])
                except:
                    batch['masterStations'] = {}
            else:
                batch['masterStations'] = {}
            batches.append(batch)
        
        conn.close()
        return jsonify(batches)
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/stats', methods=['GET'])
def get_stats():
    """Получить статистику"""
    try:
        conn = get_db()
        cursor = conn.cursor()
        
        cursor.execute('SELECT status, COUNT(*) as count FROM batches GROUP BY status')
        stats = {}
        for row in cursor.fetchall():
            stats[row[0]] = row[1]
        
        conn.close()
        return jsonify(stats)
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/batches/export', methods=['GET'])
def export_data():
    """Экспортировать все данные"""
    try:
        conn = get_db()
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM batches ORDER BY created DESC')
        
        batches = []
        for row in cursor.fetchall():
            batch = dict(row)
            batch['groups'] = json.loads(batch['groups'])
            if batch.get('masterStations'):
                try:
                    batch['masterStations'] = json.loads(batch['masterStations'])
                except:
                    batch['masterStations'] = {}
            else:
                batch['masterStations'] = {}
            batches.append(batch)
        
        conn.close()
        return jsonify({
            'production': [b for b in batches if b['status'] == 'production'],
            'todo': [b for b in batches if b['status'] == 'todo'],
            'progress': [b for b in batches if b['status'] == 'progress'],
            'done': [b for b in batches if b['status'] == 'done']
        })
    except Exception as e:
        return jsonify({'error': str(e)}), 500

def parse_ifc_column_21(ifc_file_path):
    """Парсить IFC файл и извлечь данные из столбца 21 (если доступно) или из других объектов"""
    try:
        ifc = ifcopenshell.open(ifc_file_path)
        values = []
        
        # Пытаемся найти объекты со скалярными значениями
        # В IFC столбец 21 обычно соответствует определённому атрибуту
        # Будем искать числовые значения в различных IFC сущностях
        
        # Попытка 1: Ищем IfcProperty и IfcQuantity объекты с числовыми значениями
        for entity in ifc.by_type('IfcProperty'):
            try:
                if hasattr(entity, 'NominalValue') and entity.NominalValue:
                    val = entity.NominalValue.wrappedValue if hasattr(entity.NominalValue, 'wrappedValue') else entity.NominalValue
                    if isinstance(val, (int, float)):
                        values.append(float(val))
            except:
                pass
        
        # Попытка 2: Ищем IfcQuantityLength, IfcQuantityArea, IfcQuantityVolume
        for entity in ifc.by_type('IfcPhysicalQuantity'):
            try:
                # У этих объектов есть атрибут для значения количества
                for attr_name in ['Length', 'Area', 'Volume', 'Weight', 'Count']:
                    if hasattr(entity, attr_name):
                        val = getattr(entity, attr_name)
                        if isinstance(val, (int, float)):
                            values.append(float(val))
                        break
            except:
                pass
        
        # Попытка 3: Ищем простые числовые атрибуты в главных объектах
        if not values:
            for entity in ifc.by_type('IfcBuildingElementProxy'):
                try:
                    if hasattr(entity, 'ObjectType') and isinstance(entity.ObjectType, (int, float)):
                        values.append(float(entity.ObjectType))
                except:
                    pass
        
        print(f"📊 IFC: Извлечено {len(values)} значений из файла")
        return values
    except Exception as e:
        print(f"Ошибка парсинга IFC файла: {e}")
        return []

@app.route('/api/batches/import', methods=['POST'])
def import_data():
    """Импортировать данные из Excel файлов"""
    try:
        if request.content_type and 'application/json' in request.content_type:
            data = request.get_json()
            if not isinstance(data, dict):
                return jsonify({'error': 'Данные должны быть объектом'}), 400
            conn = get_db()
            cursor = conn.cursor()
            cursor.execute('DELETE FROM batches')
            for status in ['production', 'todo', 'progress', 'done']:
                for batch in data.get(status, []):
                    errors = validate_batch_data(batch)
                    if errors:
                        conn.close()
                        return jsonify({'error': 'Ошибка валидации', 'details': errors}), 400
                    cursor.execute('''
                        INSERT INTO batches (title, status, groups, scrap, masterStations)
                        VALUES (?, ?, ?, ?, ?)
                    ''', (
                        batch.get('title', '').strip(),
                        status,
                        json.dumps(batch.get('groups', [])),
                        int(batch.get('scrap', 0)),
                        json.dumps(batch.get('masterStations', {}))
                    ))
            conn.commit()
            conn.close()
            return jsonify({'status': 'imported'})

        # Множественная загрузка Excel и IFC файлов
        if 'file' in request.files:
            files = request.files.getlist('file')
            range_str = request.form.get('range', '')

            # Диапазоны ёмкости (mAh)
            ranges = [
                (17000, 17169.9), (17170, 17339.9), (17340, 17509.9), (17510, 17679.9),
                (17680, 17849.9), (17850, 18019.9), (18020, 18189.9), (18190, 18359.9),
                (18360, 18529.9), (18530, 18699.9), (18700, 18869.9), (18870, 19039.9)
            ]

            groups = [0] * len(ranges)
            scrap_count = 0

            for f in files:
                tmpfd, tmpname = tempfile.mkstemp(suffix=os.path.splitext(f.filename)[1])
                try:
                    with os.fdopen(tmpfd, 'wb') as tmpf:
                        tmpf.write(f.read())

                    values = []
                    file_ext = os.path.splitext(f.filename)[1].lower()

                    # Обработка IFC файлов
                    if file_ext == '.ifc':
                        values = parse_ifc_column_21(tmpname)
                    # Обработка Excel файлов
                    else:
                        wb = load_workbook(tmpname, data_only=True)
                        ws = wb.active

                        if range_str:
                            try:
                                from openpyxl.utils.cell import range_boundaries

                                if ':' in range_str:
                                    min_col, min_row, max_col, max_row = range_boundaries(range_str)
                                else:
                                    min_col, min_row, max_col, max_row = 1, 1, ws.max_column, ws.max_row

                                if max_row == 1 and ':' in range_str and len(range_str.split(':')[1]) <= 2:
                                    max_row = ws.max_row

                                for row in ws.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col, values_only=True):
                                    for cell in row:
                                        if cell is None:
                                            continue
                                        try:
                                            num = float(cell)
                                            values.append(num)
                                        except:
                                            continue
                            except Exception as e:
                                print(f"Ошибка парсинга диапазона {f.filename}: {e}, берём все значения")
                                for row in ws.iter_rows(values_only=True):
                                    for cell in row:
                                        if cell is None:
                                            continue
                                        try:
                                            num = float(cell)
                                            values.append(num)
                                        except:
                                            continue
                        else:
                            for row in ws.iter_rows(values_only=True):
                                for cell in row:
                                    if cell is None:
                                        continue
                                    try:
                                        num = float(cell)
                                        values.append(num)
                                    except:
                                        continue

                    print(f"📊 DEBUG: {f.filename} прочитано {len(values)} значений")

                    # Агрегируем по диапазонам
                    cell_counts = [int(v) for v in values if isinstance(v, (int, float))]
                    for val in cell_counts:
                        if val < 17000:
                            scrap_count += 1
                        else:
                            placed = False
                            for idx, (low, high) in enumerate(ranges):
                                if low <= val <= high:
                                    groups[idx] += 1
                                    placed = True
                                    break
                            if not placed:
                                groups[-1] += 1

                    print(f"📊 DEBUG: {f.filename} -> scrap+={len([v for v in cell_counts if v < 17000])}")

                finally:
                    try:
                        os.remove(tmpname)
                    except:
                        pass

            print(f"📊 DEBUG: итого scrap={scrap_count}, groups={groups}")

            batch_id = request.form.get('batch_id')
            if batch_id:
                try:
                    bid = int(batch_id)
                except:
                    return jsonify({'error': 'batch_id должен быть числом'}), 400

                conn = get_db()
                cursor = conn.cursor()
                cursor.execute('SELECT * FROM batches WHERE id = ?', (bid,))
                row = cursor.fetchone()
                if not row:
                    conn.close()
                    return jsonify({'error': 'Партия не найдена'}), 404

                cursor.execute('UPDATE batches SET groups = ?, scrap = ?, updated = CURRENT_TIMESTAMP WHERE id = ?', (
                    json.dumps([{'count': int(c)} for c in groups]), scrap_count, bid
                ))
                conn.commit()
                conn.close()

                return jsonify({'status': 'updated', 'batch_id': bid})

            title = request.form.get('title') or (files[0].filename if files else 'import')
            status = request.form.get('status') or 'todo'

            batch = {
                'title': title,
                'status': status,
                'groups': [{'count': int(c)} for c in groups],
                'scrap': scrap_count,
                'masterStations': {}
            }

            conn = get_db()
            cursor = conn.cursor()
            cursor.execute('INSERT INTO batches (title, status, groups, scrap, masterStations) VALUES (?, ?, ?, ?, ?)', (
                batch['title'], batch['status'], json.dumps(batch['groups']), batch['scrap'], json.dumps(batch['masterStations'])
            ))
            conn.commit()
            conn.close()

            return jsonify({'status': 'imported', 'batch': batch})

        return jsonify({'error': 'Неподдерживаемый формат запроса'}), 400
    except Exception as e:
        print(f"Ошибка импорта: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/health', methods=['GET'])
def health():
    """Проверка здоровья сервера"""
    return jsonify({'status': 'ok', 'message': 'Kanban API сервер работает'})

@app.route('/')
def index():
    """Раздача index.html"""
    return send_from_directory('.', 'index.html')

@app.route('/<path:filename>')
def serve_static(filename):
    """Раздача статических файлов с правильными MIME типами"""
    try:
        mimetype, _ = mimetypes.guess_type(filename)
        return send_from_directory('.', filename, mimetype=mimetype)
    except:
        return send_from_directory('.', filename)

if __name__ == '__main__':
    app.run(debug=False, host='0.0.0.0', port=5000, use_reloader=False)