시골약사
2fddc89bca
✨ 주요 기능 - 환자 관리: 환자 등록 및 조회 (이름, 전화번호, 주민번호, 성별) - 입고 관리: Excel 파일 업로드로 대량 입고 처리 - 처방 관리: 약속 처방 템플릿 등록 및 관리 - 조제 관리: 처방 기반 조제 및 약재 가감 기능 - 재고 관리: 실시간 재고 현황 및 로트별 관리 🛠️ 기술 스택 - Backend: Flask (Python 웹 프레임워크) - Database: SQLite (경량 관계형 데이터베이스) - Frontend: Bootstrap + jQuery - Excel 처리: pandas + openpyxl 🔧 핵심 개념 - 1제 = 20첩 = 30파우치 (기본값) - FIFO 방식 재고 차감 - 로트별 원산지/단가 관리 - 정확한 조제 원가 계산 📁 프로젝트 구조 - app.py: Flask 백엔드 서버 - database/: 데이터베이스 스키마 및 파일 - templates/: HTML 템플릿 - static/: JavaScript 및 CSS - sample/: 샘플 Excel 파일 🤖 Generated with Claude Code Co-Authored-By: Claude <noreply@anthropic.com>
294 lines
11 KiB
Markdown
294 lines
11 KiB
Markdown
1) 핵심 개념 정리
|
||
단위/판매 단위
|
||
1제 = 20첩 = 30파우치
|
||
|
||
1파우치 = 2/3첩
|
||
|
||
ERP에서 원가/마진 계산은 결국 “파우치 1개당 원가” 로 떨어지게 설계하면 현장 감각이 맞습니다.
|
||
|
||
약재(원재료) 관리 방식
|
||
약재는 입고 시 원산지/업체/입고시점/그람당 단가/입고량(g) 을 가진 로트(lot) 로 쌓이고
|
||
|
||
조제 시 처방 구성(예: 숙지황 5g, 작약 3g …)을 기준으로 로트를 차감합니다.
|
||
|
||
차감 정책은 보통 FEFO(유통기한 우선) 또는 FIFO.
|
||
|
||
VAT(부가세)
|
||
질문에서 “기본 포함금액”이라 했으니 DB는 기본을 VAT 포함 단가로 저장하고,
|
||
|
||
필요하면 vat_included + vat_rate 로 “세금계산서/관리용”으로만 분리 계산할 수 있게 합니다.
|
||
|
||
2) 테이블 설계 (권장: “전표/로트/원장” 3층 구조)
|
||
A. 마스터
|
||
suppliers (도매상/입고업체)
|
||
|
||
herb_items (약재 마스터: 첩약보험 9자리 코드가 핵심 FK)
|
||
|
||
(옵션) origins (원산지 표준화)
|
||
|
||
B. 입고장(매입 전표)
|
||
purchase_receipts (입고장 헤더)
|
||
|
||
purchase_receipt_lines (입고장 라인: 약재별 단가/수량/원산지)
|
||
|
||
C. 재고(로트)
|
||
inventory_lots
|
||
|
||
입고 라인 1건 → 로트 1건 생성(또는 같은 조건이면 합산 로트 정책 가능)
|
||
|
||
로트에 “현재 잔량”을 두고 즉시 조회가 빠르게.
|
||
|
||
D. 재고 원장(증감 기록, 트랜잭션 근거)
|
||
stock_ledger
|
||
|
||
입고(+), 조제(-), 조정(+/-), 폐기(-) 모두 기록
|
||
|
||
정합성의 근거는 원장, inventory_lots.qty_onhand_g 는 캐시(빠른 조회용)로 유지
|
||
|
||
E. 조제/처방(보험코드 기반)
|
||
formulas (처방 마스터: “쌍화탕” 등, 보험코드)
|
||
|
||
formula_ingredients (구성 약재: herb_item + grams_per_첩 등)
|
||
|
||
F. 조제 실행(배치/제조)
|
||
compounds (조제 작업 헤더: 몇 제, 몇 파우치, 판매/조제 일자)
|
||
|
||
compound_consumptions (로트별 차감 내역: lot_id, qty_used_g)
|
||
|
||
이렇게 하면:
|
||
|
||
입고장은 회계/증빙
|
||
|
||
재고는 로트로 실물 관리
|
||
|
||
조제는 로트에서 차감
|
||
|
||
모든 변화는 원장으로 추적 가능 → 감사/오류 수정/원가추적에 강함
|
||
|
||
3) DDL 예시 (PostgreSQL 기준, SQLite에서도 거의 그대로 사용 가능)
|
||
SQLite는 NUMERIC/DECIMAL을 내부적으로 유연하게 저장하니, 정밀도는 애플리케이션에서 관리(또는 정수로 “원/그램×1000” 방식)하면 더 안전합니다.
|
||
|
||
-- 1) Suppliers
|
||
CREATE TABLE suppliers (
|
||
supplier_id INTEGER PRIMARY KEY,
|
||
name TEXT NOT NULL,
|
||
biz_no TEXT,
|
||
contact TEXT,
|
||
phone TEXT,
|
||
address TEXT,
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
||
);
|
||
|
||
-- 2) Herb items (첩약보험 9자리 코드)
|
||
CREATE TABLE herb_items (
|
||
herb_item_id INTEGER PRIMARY KEY,
|
||
insurance_code9 TEXT NOT NULL UNIQUE, -- 9자리
|
||
herb_name TEXT NOT NULL,
|
||
spec TEXT, -- 규격/품질 등
|
||
default_unit TEXT NOT NULL DEFAULT 'g',
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
||
);
|
||
|
||
-- 3) Purchase receipt header
|
||
CREATE TABLE purchase_receipts (
|
||
receipt_id INTEGER PRIMARY KEY,
|
||
supplier_id INTEGER NOT NULL,
|
||
receipt_date TEXT NOT NULL, -- 입고 시점(YYYY-MM-DD)
|
||
vat_included INTEGER NOT NULL DEFAULT 1,
|
||
vat_rate NUMERIC NOT NULL DEFAULT 0.10,
|
||
note TEXT,
|
||
source_file TEXT, -- xls 파일명/해시 등
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
|
||
);
|
||
|
||
-- 4) Purchase receipt lines
|
||
CREATE TABLE purchase_receipt_lines (
|
||
line_id INTEGER PRIMARY KEY,
|
||
receipt_id INTEGER NOT NULL,
|
||
herb_item_id INTEGER NOT NULL,
|
||
origin_country TEXT, -- 원산지
|
||
qty_g NUMERIC NOT NULL, -- 구입량(g)
|
||
unit_price_per_g NUMERIC NOT NULL, -- g당 단가 (VAT 포함 기준)
|
||
line_total NUMERIC, -- qty_g * unit_price_per_g (캐시)
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
FOREIGN KEY (receipt_id) REFERENCES purchase_receipts(receipt_id),
|
||
FOREIGN KEY (herb_item_id) REFERENCES herb_items(herb_item_id)
|
||
);
|
||
|
||
-- 5) Inventory lots (입고 라인 기반 로트)
|
||
CREATE TABLE inventory_lots (
|
||
lot_id INTEGER PRIMARY KEY,
|
||
herb_item_id INTEGER NOT NULL,
|
||
supplier_id INTEGER NOT NULL,
|
||
receipt_line_id INTEGER NOT NULL UNIQUE,
|
||
received_date TEXT NOT NULL,
|
||
origin_country TEXT,
|
||
unit_price_per_g NUMERIC NOT NULL,
|
||
qty_received_g NUMERIC NOT NULL,
|
||
qty_onhand_g NUMERIC NOT NULL,
|
||
expiry_date TEXT, -- 있으면 FEFO 가능
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
FOREIGN KEY (herb_item_id) REFERENCES herb_items(herb_item_id),
|
||
FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id),
|
||
FOREIGN KEY (receipt_line_id) REFERENCES purchase_receipt_lines(line_id)
|
||
);
|
||
|
||
-- 6) Stock ledger (재고 원장)
|
||
CREATE TABLE stock_ledger (
|
||
ledger_id INTEGER PRIMARY KEY,
|
||
event_time TEXT NOT NULL DEFAULT (datetime('now')),
|
||
event_type TEXT NOT NULL, -- 'RECEIPT','CONSUME','ADJUST','DISCARD'
|
||
herb_item_id INTEGER NOT NULL,
|
||
lot_id INTEGER, -- 로트 단위 증감이면 기록
|
||
qty_delta_g NUMERIC NOT NULL, -- +입고, -차감
|
||
unit_cost_per_g NUMERIC, -- 원가 추적용(입고/차감 시점)
|
||
ref_table TEXT, -- 'purchase_receipts','compounds' 등
|
||
ref_id INTEGER,
|
||
note TEXT,
|
||
FOREIGN KEY (herb_item_id) REFERENCES herb_items(herb_item_id),
|
||
FOREIGN KEY (lot_id) REFERENCES inventory_lots(lot_id)
|
||
);
|
||
|
||
-- 7) Formulas (처방 마스터)
|
||
CREATE TABLE formulas (
|
||
formula_id INTEGER PRIMARY KEY,
|
||
insurance_code9 TEXT NOT NULL UNIQUE, -- 처방 보험코드(요구사항)
|
||
formula_name TEXT NOT NULL, -- 예: 쌍화탕
|
||
base_pouches_per_je INTEGER NOT NULL DEFAULT 30,
|
||
base_cheop_per_je INTEGER NOT NULL DEFAULT 20,
|
||
note TEXT,
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
||
);
|
||
|
||
-- 8) Formula ingredients (구성 약재)
|
||
-- grams_per_cheop: 1첩 기준 몇 g
|
||
CREATE TABLE formula_ingredients (
|
||
formula_ingredient_id INTEGER PRIMARY KEY,
|
||
formula_id INTEGER NOT NULL,
|
||
herb_item_id INTEGER NOT NULL,
|
||
grams_per_cheop NUMERIC NOT NULL,
|
||
FOREIGN KEY (formula_id) REFERENCES formulas(formula_id),
|
||
FOREIGN KEY (herb_item_id) REFERENCES herb_items(herb_item_id),
|
||
UNIQUE (formula_id, herb_item_id)
|
||
);
|
||
|
||
-- 9) Compound batch (조제 실행)
|
||
CREATE TABLE compounds (
|
||
compound_id INTEGER PRIMARY KEY,
|
||
formula_id INTEGER NOT NULL,
|
||
compound_date TEXT NOT NULL,
|
||
je_count NUMERIC NOT NULL, -- 1제, 0.5제 등 가능하게
|
||
cheop_total NUMERIC NOT NULL, -- = je_count * 20
|
||
pouch_total NUMERIC NOT NULL, -- = je_count * 30 (기본) 또는 입력
|
||
sell_price_total NUMERIC, -- 매출 총액(선택)
|
||
note TEXT,
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
FOREIGN KEY (formula_id) REFERENCES formulas(formula_id)
|
||
);
|
||
|
||
-- 10) Compound consumption lines (로트별 차감)
|
||
CREATE TABLE compound_consumptions (
|
||
consumption_id INTEGER PRIMARY KEY,
|
||
compound_id INTEGER NOT NULL,
|
||
herb_item_id INTEGER NOT NULL,
|
||
lot_id INTEGER NOT NULL,
|
||
qty_used_g NUMERIC NOT NULL,
|
||
unit_cost_per_g NUMERIC NOT NULL,
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
FOREIGN KEY (compound_id) REFERENCES compounds(compound_id),
|
||
FOREIGN KEY (herb_item_id) REFERENCES herb_items(herb_item_id),
|
||
FOREIGN KEY (lot_id) REFERENCES inventory_lots(lot_id)
|
||
);
|
||
4) “입고장 자동 작성(XLS)” 처리 흐름
|
||
XLS에서 들어온 컬럼(예시)
|
||
입고일자, 약재명, 보험코드9, 원산지, 구입량(g), g당 단가, 도매상(업체명), (옵션) VAT 포함여부
|
||
|
||
자동 처리 로직
|
||
supplier(도매상) 매칭/없으면 생성
|
||
|
||
herb_items는 보험코드9 기준으로 upsert
|
||
|
||
보험코드9이 없고 약재명만 있으면: “임시코드/매핑 대기” 상태로 넣고 추후 매칭
|
||
|
||
purchase_receipts 헤더 생성(파일 단위 또는 날짜/업체 단위로 묶기)
|
||
|
||
purchase_receipt_lines 생성
|
||
|
||
각 line마다 inventory_lots 생성 + lot.qty_onhand_g = qty_received_g
|
||
|
||
stock_ledger에 RECEIPT (+qty) 기록
|
||
|
||
“B. 도매상 선택(입고장 입력)”은 receipt 헤더에 supplier_id를 두면 해결됩니다.
|
||
XLS 자체가 입고장 형태면 “파일 1개 = 입고장 1개”, 아니면 “업체+날짜”로 자동 그룹핑 추천.
|
||
|
||
5) 조제 시 재고 차감(트랜잭션 처리 핵심)
|
||
조제 계산
|
||
cheop_total = je_count * 20
|
||
|
||
각 약재 소요량(g) = grams_per_cheop * cheop_total
|
||
|
||
예: 숙지황 5g/첩, 1제(20첩)면 100g 소요
|
||
|
||
로트 선택(예: FEFO)
|
||
해당 herb_item의 lot 중 qty_onhand_g > 0 인 것들을
|
||
|
||
expiry_date ASC NULLS LAST, received_date ASC 순으로 소비
|
||
|
||
필요한 g를 로트 여러 개로 쪼개 차감 가능
|
||
|
||
기록
|
||
compounds 1건 생성
|
||
|
||
compound_consumptions에 “lot별 사용량” 생성
|
||
|
||
inventory_lots.qty_onhand_g 감소
|
||
|
||
stock_ledger에 CONSUME (-qty) 기록
|
||
|
||
왜 “로트별 소비 테이블”이 꼭 필요하냐?
|
||
원가 계산(가중/실제)을 정확히 하려면 어느 로트(단가)에서 몇 g를 썼는지가 필요
|
||
|
||
나중에 “원산지/업체별 사용량” 리포트도 가능
|
||
|
||
6) 원가/마진 계산(파우치 기준)
|
||
조제 1건(compound)의 재료 원가
|
||
material_cost = SUM(compound_consumptions.qty_used_g * unit_cost_per_g)
|
||
|
||
파우치 1개당 원가
|
||
cost_per_pouch = material_cost / pouch_total
|
||
|
||
마진(매출이 있을 때)
|
||
gross_profit = sell_price_total - material_cost
|
||
|
||
gross_margin = gross_profit / sell_price_total
|
||
|
||
한약은 “부재료/포장재/인건비/탕전 수수료” 같은 간접비가 큼 →
|
||
추후 overhead_rules (파우치당 포장비, 제당 탕전비 등) 테이블을 추가하면 확장 쉬워요.
|
||
|
||
7) SQLite → PostgreSQL 이관 팁 (처음부터 이렇게 해두면 편함)
|
||
ID는 INTEGER PRIMARY KEY(SQLite)로 시작해도 Postgres에서 BIGSERIAL/IDENTITY로 쉽게 변환
|
||
|
||
날짜/시간은 SQLite에서는 TEXT(ISO8601), Postgres에서는 timestamptz/date로 변경
|
||
|
||
금액/단가는 Postgres에서 numeric(12,4) 같은 식으로 고정 권장
|
||
|
||
8) 다음 스텝(바로 구현 순서 제안)
|
||
XLS 입고 Import부터: suppliers/herb_items/receipt/lot/ledger까지 자동 생성
|
||
|
||
처방 등록: formulas + formula_ingredients (보험코드9 기반)
|
||
|
||
조제 실행: compound 생성 → 필요량 계산 → 로트 차감/원장 기록
|
||
|
||
리포트: 재고현황(약재별 합계), 로트별 재고, 월별 매입/사용/마진
|
||
|
||
원하면 다음 메시지에서 내가 바로 이어서:
|
||
|
||
“XLS 컬럼 예시(샘플 헤더)” 를 기준으로 import 매핑표,
|
||
|
||
FEFO/FIFO 차감 SQL(또는 파이썬 로직),
|
||
|
||
“입고장 자동 그룹핑 규칙(파일단위/업체+날짜단위)”
|
||
까지 한 번에 “구현 가능한 수준”으로 적어줄게요.
|
||
|
||
우선, 실제 XLS에 보험코드9 열이 항상 있나, 아니면 약재명만 있고 보험코드 매핑이 따로 필요한지 이 두 케이스 중 어느 쪽이 더 많아요? (둘 다 가능하도록 설계는 해뒀고, import 로직만 달라져요.) |