kdrug-inventory-system/add_prescription_data.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
처방 데이터 추가 스크립트
- 소청룡탕, 갈근탕 등 처방 데이터 추가
"""

import sqlite3
from datetime import datetime

def get_connection():
    """데이터베이스 연결"""
    return sqlite3.connect('database/kdrug.db')

def add_prescriptions():
    """소청룡탕과 갈근탕 처방 추가"""
    conn = get_connection()
    cursor = conn.cursor()

    # 처방 데이터 정의
    prescriptions = [
        {
            'formula_code': 'SCR001',
            'formula_name': '소청룡탕',
            'formula_type': 'STANDARD',
            'base_cheop': 1,
            'base_pouches': 1,
            'description': '외감풍한, 내정수음으로 인한 기침, 천식을 치료하는 처방. 한담을 풀어내고 기침을 멎게 함.',
            'ingredients': [
                {'code': '3147H1AHM', 'amount': 6.0, 'notes': '발한해표'},  # 마황
                {'code': '3419H1AHM', 'amount': 6.0, 'notes': '화영지통'},  # 백작약
                {'code': '3342H1AHM', 'amount': 6.0, 'notes': '렴폐지해'},  # 오미자
                {'code': '3182H1AHM', 'amount': 6.0, 'notes': '화담지구'},  # 반하
                {'code': '3285H1AHM', 'amount': 4.0, 'notes': '온폐산한'},  # 세신
                {'code': '3017H1AHM', 'amount': 4.0, 'notes': '온중산한'},  # 건강
                {'code': '3033H1AHM', 'amount': 4.0, 'notes': '해표발한'},  # 계지
                {'code': '3007H1AHM', 'amount': 4.0, 'notes': '조화제약'},  # 감초
            ]
        },
        {
            'formula_code': 'GGT001',
            'formula_name': '갈근탕',
            'formula_type': 'STANDARD',
            'base_cheop': 1,
            'base_pouches': 1,
            'description': '외감풍한으로 인한 두통, 발열, 오한, 항강을 치료하는 처방. 발한해표하고 승진해기함.',
            'ingredients': [
                {'code': '3002H1AHM', 'amount': 8.0, 'notes': '승진해기'},  # 갈근
                {'code': '3147H1AHM', 'amount': 6.0, 'notes': '발한해표'},  # 마황
                {'code': '3115H1AHM', 'amount': 6.0, 'notes': '보중익기'},  # 대조(대추)
                {'code': '3033H1AHM', 'amount': 4.0, 'notes': '해표발한'},  # 계지
                {'code': '3419H1AHM', 'amount': 4.0, 'notes': '화영지통'},  # 작약
                {'code': '3007H1AHM', 'amount': 4.0, 'notes': '조화제약'},  # 감초
                {'code': '3017H1AHM', 'amount': 2.0, 'notes': '온중산한'},  # 건강
            ]
        }
    ]

    try:
        for prescription in prescriptions:
            # 1. formulas 테이블에 처방 추가
            cursor.execute("""
                INSERT INTO formulas (
                    formula_code, formula_name, formula_type, base_cheop, base_pouches,
                    description, is_active, created_at, updated_at
                ) VALUES (?, ?, ?, ?, ?, ?, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
            """, (
                prescription['formula_code'],
                prescription['formula_name'],
                prescription['formula_type'],
                prescription['base_cheop'],
                prescription['base_pouches'],
                prescription['description']
            ))

            formula_id = cursor.lastrowid
            print(f"[추가됨] {prescription['formula_name']} 처방 추가 완료 (ID: {formula_id})")

            # 2. formula_ingredients 테이블에 구성 약재 추가
            for ingredient in prescription['ingredients']:
                # 약재 이름 조회 (로그용)
                cursor.execute("""
                    SELECT herb_name FROM herb_masters
                    WHERE ingredient_code = ?
                """, (ingredient['code'],))
                herb_name_result = cursor.fetchone()
                herb_name = herb_name_result[0] if herb_name_result else 'Unknown'

                cursor.execute("""
                    INSERT INTO formula_ingredients (
                        formula_id, ingredient_code, grams_per_cheop, notes,
                        sort_order, created_at
                    ) VALUES (?, ?, ?, ?, 0, CURRENT_TIMESTAMP)
                """, (
                    formula_id,
                    ingredient['code'],
                    ingredient['amount'],
                    ingredient['notes']
                ))

                print(f"  - {herb_name}({ingredient['code']}): {ingredient['amount']}g - {ingredient['notes']}")

        conn.commit()
        print("\n[완료] 모든 처방 데이터가 성공적으로 추가되었습니다!")

    except Exception as e:
        conn.rollback()
        print(f"\n[오류] 오류 발생: {e}")
        import traceback
        traceback.print_exc()
    finally:
        conn.close()

def verify_prescriptions():
    """추가된 처방 데이터 확인"""
    conn = get_connection()
    cursor = conn.cursor()

    print("\n" + "="*80)
    print("추가된 처방 데이터 확인")
    print("="*80)

    # 추가된 처방 목록 확인
    cursor.execute("""
        SELECT f.formula_id, f.formula_code, f.formula_name, f.formula_type, f.description,
               COUNT(fi.ingredient_id) as ingredient_count,
               SUM(fi.grams_per_cheop) as total_amount
        FROM formulas f
        LEFT JOIN formula_ingredients fi ON f.formula_id = fi.formula_id
        WHERE f.formula_code IN ('SCR001', 'GGT001')
        GROUP BY f.formula_id
    """)

    for row in cursor.fetchall():
        print(f"\n[처방] {row[2]} ({row[1]})")
        print(f"   타입: {row[3]}")
        print(f"   설명: {row[4]}")
        print(f"   구성약재: {row[5]}가지")
        print(f"   총 용량: {row[6]}g")

        # 구성 약재 상세
        cursor.execute("""
            SELECT hm.herb_name, fi.ingredient_code, fi.grams_per_cheop, fi.notes
            FROM formula_ingredients fi
            JOIN herb_masters hm ON fi.ingredient_code = hm.ingredient_code
            WHERE fi.formula_id = ?
            ORDER BY fi.grams_per_cheop DESC
        """, (row[0],))

        print("   구성 약재:")
        for ingredient in cursor.fetchall():
            print(f"     - {ingredient[0]}({ingredient[1]}): {ingredient[2]}g - {ingredient[3]}")

    conn.close()

def main():
    print("="*80)
    print("처방 데이터 추가 스크립트")
    print("="*80)

    # 처방 추가
    add_prescriptions()

    # 추가된 데이터 확인
    verify_prescriptions()

if __name__ == "__main__":
    main()