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 읽기 개선
```python
# 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단계: 보험코드 → 성분코드 매핑
```python
# 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
```

## 성분코드 기준 재고 관리

### 재고 조회 쿼리
```sql
-- 성분코드별 통합 재고 조회
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;
```

### 조제 시 제품 선택
```sql
-- 성분코드로 가용 제품 조회
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 정기 업데이트
- 신규 보험코드 추가 프로세스
- 성분코드 변경 이력 관리