kdrug-inventory-system/dev_scripts/final_price_analysis.py

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

"""
최종 가격 차이 분석
"""

import sqlite3

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

    print("=" * 80)
    print("📊 재고 자산 차이 최종 분석")
    print("=" * 80)
    print()

    # 1. 핵심 차이 확인
    print("1. 핵심 금액 차이")
    print("-" * 60)

    # 입고 라인과 LOT 차이
    cursor.execute("""
        SELECT
            h.herb_name,
            prl.quantity_g as receipt_qty,
            prl.unit_price_per_g as receipt_price,
            prl.line_total as receipt_total,
            il.quantity_received as lot_qty,
            il.unit_price_per_g as lot_price,
            il.quantity_received * il.unit_price_per_g as lot_total,
            prl.line_total - (il.quantity_received * il.unit_price_per_g) as 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.line_total - (il.quantity_received * il.unit_price_per_g)) > 1
        ORDER BY ABS(prl.line_total - (il.quantity_received * il.unit_price_per_g)) DESC
    """)

    differences = cursor.fetchall()

    if differences:
        print("  입고장과 LOT 생성 시 차이가 있는 항목:")
        print()
        total_diff = 0
        for diff in differences:
            print(f"  📌 {diff['herb_name']}")
            print(f"     입고장: {diff['receipt_qty']:,.0f}g × ₩{diff['receipt_price']:.2f} = ₩{diff['receipt_total']:,.0f}")
            print(f"     LOT:    {diff['lot_qty']:,.0f}g × ₩{diff['lot_price']:.2f} = ₩{diff['lot_total']:,.0f}")
            print(f"     차이:   ₩{diff['diff']:,.0f}")
            print()
            total_diff += diff['diff']

        print(f"  총 차이: ₩{total_diff:,.0f}")

    # 2. 재고 자산 흐름
    print("\n2. 재고 자산 흐름 정리")
    print("=" * 60)

    # 각 단계별 금액
    cursor.execute("SELECT SUM(line_total) as total FROM purchase_receipt_lines")
    receipt_total = cursor.fetchone()['total'] or 0

    cursor.execute("""
        SELECT SUM(quantity_received * unit_price_per_g) as total
        FROM inventory_lots
    """)
    lot_creation_total = cursor.fetchone()['total'] or 0

    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
    """)
    consumed_total = cursor.fetchone()['total'] or 0

    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_total:,.0f}")
    print(f"     차이 (1-2):        ₩{receipt_total - lot_creation_total:,.0f}")
    print()
    print(f"  3️⃣ 소비 총액:          ₩{consumed_total:,.0f}")
    print(f"  4️⃣ 현재 재고 자산:      ₩{current_inventory:,.0f}")
    print()
    print(f"  📊 계산식:")
    print(f"     LOT 생성 - 소비 = ₩{lot_creation_total:,.0f} - ₩{consumed_total:,.0f}")
    print(f"                    = ₩{lot_creation_total - consumed_total:,.0f} (예상)")
    print(f"     실제 재고      = ₩{current_inventory:,.0f}")
    print(f"     차이          = ₩{current_inventory - (lot_creation_total - consumed_total):,.0f}")

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

    # 휴먼일당귀 특별 케이스 확인
    cursor.execute("""
        SELECT
            prl.quantity_g as receipt_qty,
            il.quantity_received as lot_received,
            il.quantity_onhand as lot_current
        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 h.herb_name = '휴먼일당귀'
    """)

    ildan = cursor.fetchone()
    if ildan:
        print("\n  💡 휴먼일당귀 케이스:")
        print(f"     입고장 수량: {ildan['receipt_qty']:,.0f}g")
        print(f"     LOT 생성 수량: {ildan['lot_received']:,.0f}g")
        print(f"     현재 재고: {ildan['lot_current']:,.0f}g")
        print(f"     → 입고 시 5,000g 중 3,000g만 LOT 생성됨")
        print(f"     → 나머지 2,000g는 별도 처리되었을 가능성")

    print("\n  📝 결론:")
    print("     1. 입고장 총액 (₩1,616,400) vs LOT 생성 총액 (₩1,607,400)")
    print("        → ₩9,000 차이 (휴먼일당귀 수량 차이로 인함)")
    print()
    print("     2. 예상 재고 (₩1,529,434) vs 실제 재고 (₩1,529,434)")
    print("        → 정확히 일치")
    print()
    print("     3. 입고 기준 예상 (₩1,538,434) vs 실제 재고 (₩1,529,434)")
    print("        → ₩9,000 차이 (입고와 LOT 생성 차이와 동일)")

    # 4. 추가 LOT 확인
    print("\n4. 추가 LOT 존재 여부")
    print("-" * 60)

    cursor.execute("""
        SELECT
            h.herb_name,
            COUNT(*) as lot_count,
            SUM(il.quantity_received) as total_received,
            SUM(il.quantity_onhand) as total_onhand
        FROM inventory_lots il
        JOIN herb_items h ON il.herb_item_id = h.herb_item_id
        WHERE h.herb_name = '휴먼일당귀'
        GROUP BY h.herb_item_id
    """)

    ildan_lots = cursor.fetchone()
    if ildan_lots:
        print(f"  휴먼일당귀 LOT 현황:")
        print(f"    LOT 개수: {ildan_lots['lot_count']}개")
        print(f"    총 입고량: {ildan_lots['total_received']:,.0f}g")
        print(f"    현재 재고: {ildan_lots['total_onhand']:,.0f}g")

        # 상세 LOT 정보
        cursor.execute("""
            SELECT
                lot_id,
                lot_number,
                quantity_received,
                quantity_onhand,
                unit_price_per_g,
                receipt_line_id
            FROM inventory_lots il
            JOIN herb_items h ON il.herb_item_id = h.herb_item_id
            WHERE h.herb_name = '휴먼일당귀'
        """)

        lots = cursor.fetchall()
        for lot in lots:
            print(f"\n    LOT {lot['lot_id']}:")
            print(f"      LOT 번호: {lot['lot_number']}")
            print(f"      입고량: {lot['quantity_received']:,.0f}g")
            print(f"      현재: {lot['quantity_onhand']:,.0f}g")
            print(f"      단가: ₩{lot['unit_price_per_g']:.2f}")
            print(f"      입고라인: {lot['receipt_line_id']}")

    conn.close()

if __name__ == "__main__":
    final_price_analysis()