kdrug-inventory-system/docs/입고_프로세스_개선방안.md

입고 프로세스 개선 방안

현재 구조 이해

3단계 데이터 계층

1. herb_masters (454개)
   - 성분코드(ingredient_code) 기준 마스터
   - 예: 3400H1AHM = 인삼

2. herb_products (53,769개)
   - 성분코드별 보험코드 매핑 (참조 테이블)
   - 예: 060600420 = 신흥인삼 → 성분코드 3400H1AHM
   - 예: 060801010 = 세화인삼 → 성분코드 3400H1AHM

3. herb_items (40개)
   - 실제 사용/재고 관리 단위
   - ingredient_code + insurance_code 모두 보유

현재 문제점

1. Excel 입고 시 보험코드 처리 문제

• Excel에서 보험코드를 숫자로 읽음: 060600420 → 60600420
• DB 매핑 실패 → 새로운 herb_item 생성 (중복/잘못된 데이터)

2. 성분코드 연결 누락

• 입고 시 보험코드만으로 herb_item 생성
• ingredient_code 연결 안 됨
• 성분코드 기준 재고 집계 불가

개선된 입고 프로세스

1단계: Excel 읽기 개선

# excel_processor.py 수정
def read_excel(self, file_path):
    # 제품코드를 문자열로 읽기
    self.df_original = pd.read_excel(
        file_path,
        dtype={'제품코드': str}
    )

def process_hanisarang/haninfo(self):
    # 보험코드 9자리 패딩 처리
    if 'insurance_code' in df_mapped.columns:
        df_mapped['insurance_code'] = df_mapped['insurance_code'].apply(
            lambda x: str(x).zfill(9) if pd.notna(x) and str(x).isdigit() else x
        )

2단계: 보험코드 → 성분코드 매핑

# app.py의 upload_purchase_excel 함수 수정

# 1. 보험코드로 herb_products에서 성분코드 찾기
insurance_code = str(row.get('insurance_code')).zfill(9)  # 9자리 패딩

cursor.execute("""
    SELECT DISTINCT ingredient_code, product_name, company_name
    FROM herb_products
    WHERE product_code = ?
""", (insurance_code,))
product_info = cursor.fetchone()

if product_info:
    ingredient_code = product_info[0]

    # 2. herb_items에서 해당 보험코드 제품 확인
    cursor.execute("""
        SELECT herb_item_id
        FROM herb_items
        WHERE insurance_code = ?
    """, (insurance_code,))
    herb_item = cursor.fetchone()

    if not herb_item:
        # 3. 새 제품 생성 (ingredient_code 포함!)
        cursor.execute("""
            INSERT INTO herb_items (
                ingredient_code,
                insurance_code,
                herb_name,
                specification
            ) VALUES (?, ?, ?, ?)
        """, (
            ingredient_code,
            insurance_code,
            product_info[1],  # product_name
            product_info[2]   # company_name
        ))
        herb_item_id = cursor.lastrowid
    else:
        herb_item_id = herb_item[0]
else:
    # herb_products에 없는 경우 (비보험 약재 등)
    # 기존 로직 유지 또는 경고
    pass

성분코드 기준 재고 관리

재고 조회 쿼리

-- 성분코드별 통합 재고 조회
SELECT
    hm.ingredient_code,
    hm.herb_name as master_name,
    hm.herb_name_hanja,
    COUNT(DISTINCT hi.herb_item_id) as product_count,
    COUNT(DISTINCT hi.insurance_code) as insurance_code_count,
    COUNT(DISTINCT il.lot_id) as lot_count,
    COALESCE(SUM(il.quantity_onhand), 0) as total_stock,
    GROUP_CONCAT(DISTINCT hi.specification) as companies
FROM herb_masters hm
LEFT JOIN herb_items hi ON hm.ingredient_code = hi.ingredient_code
LEFT JOIN inventory_lots il ON hi.herb_item_id = il.herb_item_id
WHERE hm.is_active = 1
GROUP BY hm.ingredient_code
ORDER BY hm.herb_name;

조제 시 제품 선택

-- 성분코드로 가용 제품 조회
SELECT
    hi.herb_item_id,
    hi.insurance_code,
    hi.herb_name as product_name,
    hi.specification as company,
    SUM(il.quantity_onhand) as available_stock
FROM herb_items hi
JOIN inventory_lots il ON hi.herb_item_id = il.herb_item_id
WHERE hi.ingredient_code = ?
  AND il.quantity_onhand > 0
GROUP BY hi.herb_item_id
ORDER BY available_stock DESC;

구현 우선순위

1. 즉시 수정 (보험코드 문제 해결)

• excel_processor.py: 제품코드 문자열 처리
• app.py: 9자리 패딩 로직 추가

2. 데이터 정리

• 기존 잘못된 herb_items 정리
• ingredient_code 누락된 항목 업데이트

3. 프로세스 개선

• 입고 시 herb_products 참조하여 성분코드 자동 연결
• 성분코드 기준 재고 조회 API 추가

4. UI 개선

• 재고 현황을 성분코드 기준으로 표시
• 제품별 상세 보기 추가

기대 효과

1. 정확한 매핑: 보험코드 → 성분코드 자동 연결
2. 통합 재고 관리: 성분코드 기준으로 여러 제품의 재고 통합 관리
3. 유연한 조제: 같은 성분의 다른 제품 선택 가능
4. 데이터 일관성: 표준 코드 체계 준수

추가 고려사항

비보험 약재 처리

• herb_products에 없는 약재 입고 시
• 수동으로 성분코드 매핑 또는
• 별도 비보험 약재 테이블 관리

검증 로직

• 보험코드 형식 검증 (9자리 숫자)
• 중복 제품 생성 방지
• 성분코드 매핑 실패 시 경고

마스터 데이터 관리

• herb_products 정기 업데이트
• 신규 보험코드 추가 프로세스
• 성분코드 변경 이력 관리