kdrug-inventory-system/dev_scripts/analyze_inventory_discrepancy.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
재고 자산 금액 불일치 분석 스크립트
"""

import sqlite3
from datetime import datetime
from decimal import Decimal, getcontext

# Decimal 정밀도 설정
getcontext().prec = 10

def analyze_inventory_discrepancy():
    conn = sqlite3.connect('database/kdrug.db')
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    print("=" * 80)
    print("재고 자산 금액 불일치 분석")
    print("=" * 80)
    print()

    # 1. 현재 inventory_lots 기준 재고 자산 계산
    print("1. 현재 시스템 재고 자산 계산 (inventory_lots 기준)")
    print("-" * 60)

    cursor.execute("""
        SELECT
            SUM(quantity_onhand * unit_price_per_g) as total_value,
            COUNT(*) as lot_count,
            SUM(quantity_onhand) as total_quantity
        FROM inventory_lots
        WHERE is_depleted = 0 AND quantity_onhand > 0
    """)

    result = cursor.fetchone()
    system_total = result['total_value'] or 0

    print(f"  총 재고 자산: ₩{system_total:,.0f}")
    print(f"  총 LOT 수: {result['lot_count']}개")
    print(f"  총 재고량: {result['total_quantity']:,.1f}g")
    print()

    # 2. 원본 입고장 데이터 분석
    print("2. 입고장 기준 계산")
    print("-" * 60)

    # 전체 입고 금액
    cursor.execute("""
        SELECT
            SUM(total_price) as total_purchase,
            COUNT(*) as receipt_count,
            SUM(quantity_g) as total_quantity
        FROM purchase_receipts
    """)

    receipts = cursor.fetchone()
    total_purchase = receipts['total_purchase'] or 0

    print(f"  총 입고 금액: ₩{total_purchase:,.0f}")
    print(f"  총 입고장 수: {receipts['receipt_count']}건")
    print(f"  총 입고량: {receipts['total_quantity']:,.1f}g")
    print()

    # 3. 출고 데이터 분석
    print("3. 출고 데이터 분석")
    print("-" * 60)

    cursor.execute("""
        SELECT
            SUM(pd.quantity * il.unit_price_per_g) as total_dispensed_value,
            SUM(pd.quantity) as total_dispensed_quantity,
            COUNT(DISTINCT p.prescription_id) as prescription_count
        FROM prescription_details pd
        JOIN prescriptions p ON pd.prescription_id = p.prescription_id
        JOIN inventory_lots il ON pd.lot_id = il.lot_id
        WHERE p.status IN ('completed', 'dispensed')
    """)

    dispensed = cursor.fetchone()
    total_dispensed_value = dispensed['total_dispensed_value'] or 0

    print(f"  총 출고 금액: ₩{total_dispensed_value:,.0f}")
    print(f"  총 출고량: {dispensed['total_dispensed_quantity'] or 0:,.1f}g")
    print(f"  총 처방전 수: {dispensed['prescription_count']}건")
    print()

    # 4. 재고 보정 데이터 분석
    print("4. 재고 보정 데이터 분석")
    print("-" * 60)

    cursor.execute("""
        SELECT
            adjustment_type,
            SUM(quantity) as total_quantity,
            SUM(quantity * unit_price) as total_value,
            COUNT(*) as count
        FROM stock_adjustments
        GROUP BY adjustment_type
    """)

    adjustments = cursor.fetchall()
    total_adjustment_value = 0

    for adj in adjustments:
        adj_type = adj['adjustment_type']
        value = adj['total_value'] or 0

        # 보정 타입에 따른 금액 계산
        if adj_type in ['disposal', 'loss', 'decrease']:
            total_adjustment_value -= value
            print(f"  {adj_type}: -₩{value:,.0f} ({adj['count']}건, {adj['total_quantity']:,.1f}g)")
        else:
            total_adjustment_value += value
            print(f"  {adj_type}: +₩{value:,.0f} ({adj['count']}건, {adj['total_quantity']:,.1f}g)")

    print(f"  순 보정 금액: ₩{total_adjustment_value:,.0f}")
    print()

    # 5. 예상 재고 자산 계산
    print("5. 예상 재고 자산 계산")
    print("-" * 60)

    expected_value = total_purchase - total_dispensed_value + total_adjustment_value

    print(f"  입고 금액: ₩{total_purchase:,.0f}")
    print(f"  - 출고 금액: ₩{total_dispensed_value:,.0f}")
    print(f"  + 보정 금액: ₩{total_adjustment_value:,.0f}")
    print(f"  = 예상 재고 자산: ₩{expected_value:,.0f}")
    print()

    # 6. 차이 분석
    print("6. 차이 분석")
    print("-" * 60)

    discrepancy = system_total - expected_value
    discrepancy_pct = (discrepancy / expected_value * 100) if expected_value != 0 else 0

    print(f"  시스템 재고 자산: ₩{system_total:,.0f}")
    print(f"  예상 재고 자산: ₩{expected_value:,.0f}")
    print(f"  차이: ₩{discrepancy:,.0f} ({discrepancy_pct:+.2f}%)")
    print()

    # 7. 상세 불일치 원인 분석
    print("7. 잠재적 불일치 원인 분석")
    print("-" * 60)

    # 7-1. LOT과 입고장 매칭 확인
    cursor.execute("""
        SELECT COUNT(*) as unmatched_lots
        FROM inventory_lots il
        WHERE il.receipt_id IS NULL AND il.is_depleted = 0
    """)
    unmatched = cursor.fetchone()

    if unmatched['unmatched_lots'] > 0:
        print(f"  ⚠️ 입고장과 매칭되지 않은 LOT: {unmatched['unmatched_lots']}개")

        cursor.execute("""
            SELECT
                herb_name,
                lot_number,
                quantity_onhand,
                unit_price_per_g,
                quantity_onhand * unit_price_per_g as value
            FROM inventory_lots il
            JOIN herb_items h ON il.herb_item_id = h.herb_item_id
            WHERE il.receipt_id IS NULL AND il.is_depleted = 0
            ORDER BY value DESC
            LIMIT 5
        """)

        unmatched_lots = cursor.fetchall()
        for lot in unmatched_lots:
            print(f"    - {lot['herb_name']} (LOT: {lot['lot_number']}): ₩{lot['value']:,.0f}")

    # 7-2. 단가 변동 확인
    cursor.execute("""
        SELECT
            h.herb_name,
            MIN(il.unit_price_per_g) as min_price,
            MAX(il.unit_price_per_g) as max_price,
            AVG(il.unit_price_per_g) as avg_price,
            MAX(il.unit_price_per_g) - MIN(il.unit_price_per_g) as price_diff
        FROM inventory_lots il
        JOIN herb_items h ON il.herb_item_id = h.herb_item_id
        WHERE il.is_depleted = 0 AND il.quantity_onhand > 0
        GROUP BY h.herb_item_id, h.herb_name
        HAVING price_diff > 0
        ORDER BY price_diff DESC
        LIMIT 5
    """)

    price_variations = cursor.fetchall()
    if price_variations:
        print(f"\n  ⚠️ 단가 변동이 큰 약재 (동일 약재 다른 단가):")
        for item in price_variations:
            print(f"    - {item['herb_name']}: ₩{item['min_price']:.2f} ~ ₩{item['max_price']:.2f} (차이: ₩{item['price_diff']:.2f})")

    # 7-3. 입고장 없는 출고 확인
    cursor.execute("""
        SELECT COUNT(DISTINCT pd.lot_id) as orphan_dispenses
        FROM prescription_details pd
        LEFT JOIN inventory_lots il ON pd.lot_id = il.lot_id
        WHERE il.lot_id IS NULL
    """)
    orphan = cursor.fetchone()

    if orphan['orphan_dispenses'] > 0:
        print(f"\n  ⚠️ LOT 정보 없는 출고: {orphan['orphan_dispenses']}건")

    # 7-4. 음수 재고 확인
    cursor.execute("""
        SELECT COUNT(*) as negative_stock
        FROM inventory_lots
        WHERE quantity_onhand < 0
    """)
    negative = cursor.fetchone()

    if negative['negative_stock'] > 0:
        print(f"\n  ⚠️ 음수 재고 LOT: {negative['negative_stock']}개")

    # 8. 권장사항
    print("\n8. 권장사항")
    print("-" * 60)

    if abs(discrepancy) > 1000:
        print("  🔴 상당한 금액 차이가 발생했습니다. 다음 사항을 확인하세요:")
        print("     1) 모든 입고장이 inventory_lots에 정확히 반영되었는지 확인")
        print("     2) 출고 시 올바른 LOT과 단가가 적용되었는지 확인")
        print("     3) 재고 보정 내역이 정확히 기록되었는지 확인")
        print("     4) 초기 재고 입력 시 단가가 정확했는지 확인")

        if unmatched['unmatched_lots'] > 0:
            print(f"     5) 입고장과 매칭되지 않은 {unmatched['unmatched_lots']}개 LOT 확인 필요")
    else:
        print("  ✅ 재고 자산이 대체로 일치합니다.")

    conn.close()

if __name__ == "__main__":
    analyze_inventory_discrepancy()