kdrug-inventory-system/dev_scripts/analyze_price_difference.py

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

"""
입고 단가와 LOT 단가 차이 분석
"""

import sqlite3

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

    print("=" * 80)
    print("입고 단가와 LOT 단가 차이 상세 분석")
    print("=" * 80)
    print()

    # 1. 입고 라인과 LOT의 단가 차이 분석
    print("1. 입고 라인 vs LOT 단가 비교")
    print("-" * 60)

    cursor.execute("""
        SELECT
            h.herb_name,
            prl.line_id,
            prl.quantity_g as purchase_qty,
            prl.unit_price_per_g as purchase_price,
            prl.line_total as purchase_total,
            il.quantity_received as lot_received_qty,
            il.quantity_onhand as lot_current_qty,
            il.unit_price_per_g as lot_price,
            il.quantity_received * il.unit_price_per_g as lot_original_value,
            il.quantity_onhand * il.unit_price_per_g as lot_current_value,
            ABS(prl.unit_price_per_g - il.unit_price_per_g) as price_diff,
            prl.line_total - (il.quantity_received * il.unit_price_per_g) as value_diff
        FROM purchase_receipt_lines prl
        JOIN inventory_lots il ON prl.line_id = il.receipt_line_id
        JOIN herb_items h ON prl.herb_item_id = h.herb_item_id
        WHERE ABS(prl.unit_price_per_g - il.unit_price_per_g) > 0.01
            OR ABS(prl.quantity_g - il.quantity_received) > 0.01
        ORDER BY ABS(value_diff) DESC
    """)

    diffs = cursor.fetchall()

    if diffs:
        print(f"  ⚠️ 단가 또는 수량이 다른 항목: {len(diffs)}개\n")

        total_value_diff = 0
        for i, diff in enumerate(diffs[:10], 1):
            print(f"  {i}. {diff['herb_name']}")
            print(f"     입고: {diff['purchase_qty']:,.0f}g × ₩{diff['purchase_price']:.2f} = ₩{diff['purchase_total']:,.0f}")
            print(f"     LOT:  {diff['lot_received_qty']:,.0f}g × ₩{diff['lot_price']:.2f} = ₩{diff['lot_original_value']:,.0f}")
            print(f"     차이: ₩{diff['value_diff']:,.0f}")
            total_value_diff += diff['value_diff']
            print()

        cursor.execute("""
            SELECT SUM(prl.line_total - (il.quantity_received * il.unit_price_per_g)) as total_diff
            FROM purchase_receipt_lines prl
            JOIN inventory_lots il ON prl.line_id = il.receipt_line_id
        """)
        total_diff = cursor.fetchone()['total_diff'] or 0

        print(f"  총 차이 금액: ₩{total_diff:,.0f}")
    else:
        print("  ✅ 모든 입고 라인과 LOT의 단가/수량이 일치합니다.")

    # 2. 입고 총액과 LOT 생성 총액 비교
    print("\n2. 입고 총액 vs LOT 생성 총액")
    print("-" * 60)

    cursor.execute("""
        SELECT
            SUM(prl.line_total) as purchase_total,
            SUM(il.quantity_received * il.unit_price_per_g) as lot_creation_total
        FROM purchase_receipt_lines prl
        JOIN inventory_lots il ON prl.line_id = il.receipt_line_id
    """)

    totals = cursor.fetchone()

    print(f"  입고장 총액: ₩{totals['purchase_total']:,.0f}")
    print(f"  LOT 생성 총액: ₩{totals['lot_creation_total']:,.0f}")
    print(f"  차이: ₩{totals['purchase_total'] - totals['lot_creation_total']:,.0f}")

    # 3. 소비로 인한 차이 분석
    print("\n3. 소비 내역 상세 분석")
    print("-" * 60)

    # 복합제 소비 상세
    cursor.execute("""
        SELECT
            c.compound_name,
            h.herb_name,
            cc.quantity_used,
            il.unit_price_per_g,
            cc.quantity_used * il.unit_price_per_g as consumption_value,
            cc.consumption_date
        FROM compound_consumptions cc
        JOIN inventory_lots il ON cc.lot_id = il.lot_id
        JOIN compounds c ON cc.compound_id = c.compound_id
        JOIN herb_items h ON il.herb_item_id = h.herb_item_id
        ORDER BY consumption_value DESC
        LIMIT 10
    """)

    consumptions = cursor.fetchall()

    print("  복합제 소비 내역 (상위 10개):")
    total_consumption = 0
    for cons in consumptions:
        print(f"    - {cons['compound_name']} - {cons['herb_name']}")
        print(f"      {cons['quantity_used']:,.0f}g × ₩{cons['unit_price_per_g']:.2f} = ₩{cons['consumption_value']:,.0f}")
        total_consumption += cons['consumption_value']

    cursor.execute("""
        SELECT SUM(cc.quantity_used * il.unit_price_per_g) as total
        FROM compound_consumptions cc
        JOIN inventory_lots il ON cc.lot_id = il.lot_id
    """)
    total_consumed = cursor.fetchone()['total'] or 0

    print(f"\n  총 소비 금액: ₩{total_consumed:,.0f}")

    # 4. 재고 자산 흐름 요약
    print("\n4. 재고 자산 흐름 요약")
    print("=" * 60)

    # 입고장 기준
    cursor.execute("SELECT SUM(line_total) as total FROM purchase_receipt_lines")
    receipt_total = cursor.fetchone()['total'] or 0

    # LOT 생성 기준
    cursor.execute("""
        SELECT SUM(quantity_received * unit_price_per_g) as total
        FROM inventory_lots
        WHERE receipt_line_id IS NOT NULL
    """)
    lot_creation = cursor.fetchone()['total'] or 0

    # 현재 LOT 재고
    cursor.execute("""
        SELECT SUM(quantity_onhand * unit_price_per_g) as total
        FROM inventory_lots
        WHERE is_depleted = 0 AND quantity_onhand > 0
    """)
    current_inventory = cursor.fetchone()['total'] or 0

    print(f"  1) 입고장 총액: ₩{receipt_total:,.0f}")
    print(f"  2) LOT 생성 총액: ₩{lot_creation:,.0f}")
    print(f"     차이 (1-2): ₩{receipt_total - lot_creation:,.0f}")
    print()
    print(f"  3) 복합제 소비: ₩{total_consumed:,.0f}")
    print(f"  4) 현재 재고: ₩{current_inventory:,.0f}")
    print()
    print(f"  예상 재고 (2-3): ₩{lot_creation - total_consumed:,.0f}")
    print(f"  실제 재고: ₩{current_inventory:,.0f}")
    print(f"  차이: ₩{current_inventory - (lot_creation - total_consumed):,.0f}")

    # 5. 차이 원인 설명
    print("\n5. 차이 원인 분석")
    print("-" * 60)

    price_diff = receipt_total - lot_creation
    if abs(price_diff) > 1000:
        print(f"\n  💡 입고장과 LOT 생성 시 ₩{abs(price_diff):,.0f} 차이가 있습니다.")
        print("     가능한 원인:")
        print("     - VAT 포함/제외 계산 차이")
        print("     - 단가 반올림 차이")
        print("     - 입고 시점의 환율 적용 차이")

    consumption_diff = current_inventory - (lot_creation - total_consumed)
    if abs(consumption_diff) > 1000:
        print(f"\n  💡 예상 재고와 실제 재고 간 ₩{abs(consumption_diff):,.0f} 차이가 있습니다.")
        print("     가능한 원인:")
        print("     - 재고 보정 내역")
        print("     - 소비 시 반올림 오차 누적")
        print("     - 초기 데이터 입력 오류")

    conn.close()

if __name__ == "__main__":
    analyze_price_difference()