test2 / app.py
Sangwon78's picture
Update app.py
72b328c verified
Raw History Blame Contribute Delete
18.4 kB
import os
import openai
import gradio as gr
from openpyxl import load_workbook
from openpyxl.styles import Alignment
from openpyxl.drawing.image import Image as ExcelImage
from io import BytesIO
import tempfile
import re
######################
# (ν•„μˆ˜) OpenAI API μ„€μ •
######################
openai.api_key = os.getenv("OPENAI_API_KEY").strip()
######################
# [api자료]μ—μ„œ 제곡된 ν•¨μˆ˜ (μˆ˜μ • κΈˆμ§€)
######################
def call_api(content, system_message, max_tokens, temperature, top_p):
response = openai.ChatCompletion.create(
model="gpt-4o-mini",
messages=[
{"role": "system", "content": system_message},
{"role": "user", "content": content},
],
max_tokens=max_tokens,
temperature=temperature,
top_p=top_p,
)
return response.choices[0].message["content"]
######################
# 1) μ• λ‘œμ‚¬ν•­/λͺ©ν‘œ 뢄석
######################
def analyze_texts(difficulties, goals):
preview_difficulties = call_api(
content=difficulties,
system_message=(
"μ‚¬μš©μžκ°€ μž…λ ₯ν•œ μ• λ‘œμ‚¬ν•­μ„ μ΅œμ†Œ 4쀄 μ΄μƒμœΌλ‘œ ꡬ체적으둜 μž‘μ„±ν•΄μ€˜. "
"λ¬Έμž₯을 쀑간에 λŠμ§€ 말고, λΆˆν•„μš”ν•œ λ‚˜μ—΄(ν•˜λ‚˜, λ‘˜ λ“±) 없이 '~μŠ΅λ‹ˆλ‹€.'둜 "
"μžμ—°μŠ€λŸ½κ²Œ λλ‚΄μ€˜."
),
max_tokens=400,
temperature=0.7,
top_p=0.9,
)
preview_goals = call_api(
content=goals,
system_message=(
"μ‚¬μš©μžκ°€ μž…λ ₯ν•œ μ»¨μ„€νŒ… λͺ©ν‘œλ₯Ό μ΅œμ†Œ 4쀄 μ΄μƒμœΌλ‘œ ꡬ체적으둜 μž‘μ„±ν•΄μ€˜. "
"λ¬Έμž₯을 쀑간에 λŠμ§€ 말고, λΆˆν•„μš”ν•œ λ‚˜μ—΄(ν•˜λ‚˜, λ‘˜ λ“±) 없이 '~μŠ΅λ‹ˆλ‹€.'둜 "
"μžμ—°μŠ€λŸ½κ²Œ λλ‚΄μ€˜."
),
max_tokens=400,
temperature=0.7,
top_p=0.9,
)
return preview_difficulties, preview_goals
######################
# 2) SWOT 뢄석(쑰금 더 길게)
######################
def analyze_swot(strengths, weaknesses, opportunities, threats):
system_message = (
"당신은 swot 뢄석 μ „λ¬Έκ°€μž…λ‹ˆλ‹€. "
"μž…λ ₯된 강점, 약점, 기회, μœ„ν˜‘μ„ 각각 6~7쀄 μ •λ„λ‘œ 쑰금 더 κΈΈκ³  μžμ„Ένžˆ μš”μ•½ν•΄ μ£Όμ„Έμš”. "
"좜λ ₯은 λ°˜λ“œμ‹œ μ•„λž˜ μ˜ˆμ‹œ ν˜•νƒœμ²˜λŸΌ 총 4쀄이며, 각각은 '강점:', '약점:', '기회:', 'μœ„ν˜‘:'으둜 μ‹œμž‘ν•΄ μ£Όμ„Έμš”:\n\n"
"강점: ~~~\n"
"약점: ~~~\n"
"기회: ~~~\n"
"μœ„ν˜‘: ~~~\n\n"
"항상 '강점:', '약점:', '기회:', 'μœ„ν˜‘:'으둜 μ‹œμž‘ν•˜λŠ” 4μ€„λ‘œλ§Œ 응닡해 μ£Όμ„Έμš”."
)
content = f"""
강점: {strengths}
약점: {weaknesses}
기회: {opportunities}
μœ„ν˜‘: {threats}
"""
return call_api(
content=content,
system_message=system_message,
max_tokens=500,
temperature=0.7,
top_p=1.0
)
######################
# 3) 재무 뢄석(4쀄)
######################
def analyze_financials(
avg_monthly_sales,
rent,
labor_cost,
management_cost,
utilities,
current_interest,
other_cost,
cost_ratio
):
system_message = (
"당신은 세무사(νšŒκ³„μ‚¬)μž…λ‹ˆλ‹€. μ•„λž˜ μž…λ ₯값을 λ°”νƒ•μœΌλ‘œ κ³„μ‚°ν•œ 감가상각비, 순이읡율, "
"μ›”μˆœμˆ˜μ΅, 손읡뢄기점을 '제λͺ©: 수치' ν˜•νƒœλ‘œλ§Œ μ•Œλ €μ£Όμ„Έμš”. "
"μΆ”κ°€ μ„€λͺ… 없이 ν•­λͺ©λͺ…: κ°’ ν˜•νƒœλ§Œ."
)
content = f"""
μž…λ ₯:
- μ›”ν‰κ· λ§€μΆœμ•‘: {avg_monthly_sales}
- μž„μ°¨λ£Œ: {rent}
- 인건비: {labor_cost}
- 관리비: {management_cost}
- μˆ˜λ„κ΄‘μ—΄λΉ„: {utilities}
- μ§€κΈˆμ΄μž: {current_interest}
- κΈ°νƒ€λΉ„μš©: {other_cost}
- μ›κ°€μœ¨: {cost_ratio}
κ²°κ³Ό ν•­λͺ©:
1) 감가상각비
2) 순이읡율
3) μ›”μˆœμˆ˜μ΅
4) 손읡뢄기점
"""
return call_api(
content=content,
system_message=system_message,
max_tokens=500,
temperature=0.7,
top_p=1.0
)
######################
# 4) μƒκΆŒ 뢄석(1쀄)
######################
def analyze_market(region, industry):
system_message = (
"당신은 μƒκΆŒλΆ„μ„ μ „λ¬Έκ°€μž…λ‹ˆλ‹€. μ§€μ—­κ³Ό 업쒅에 λŒ€ν•΄ 1km 이내 μƒκΆŒ μš”μ•½. "
"사진(μ‹œκ°μžλ£Œ) μ–ΈκΈ‰ 포함, 6쀄 이내."
)
content = f"""
μ§€μ—­: {region}
μ—…μ’…: {industry}
"""
return call_api(
content=content,
system_message=system_message,
max_tokens=300,
temperature=0.7,
top_p=1.0
)
######################
# μ΅œμ’…: ν•œ λ²ˆμ— EXE.xlsx μž‘μ„±
######################
def run_all_in_one(
# (A) 상단 8개
consulting_company_rep,
consulting_address,
consulting_field,
client_name,
consulting_contact,
consulting_date_time,
consulting_round,
consulting_region,
# (B) μ• λ‘œμ‚¬ν•­/λͺ©ν‘œ
difficulties_input,
goals_input,
# (C) SWOT
strengths_input,
weaknesses_input,
opportunities_input,
threats_input,
# (D) μž¬λ¬΄λΆ„μ„(8개)
avg_monthly_sales,
rent,
labor_cost,
management_cost,
utilities,
current_interest,
other_cost,
cost_ratio,
# (E) μƒκΆŒλΆ„μ„
region_input,
industry_input,
# (F) 사진
photo1,
photo2
):
"""
기쑴 둜직 +
μ‚¬μš©μž μž…λ ₯(8개)을 E87:F87:G87 ~ E94:F94:G94 λ“± λ³‘ν•©λœ 셀에 λ„£μœΌλ € ν•˜λ©΄ MergedCell μ—λŸ¬ λ°œμƒ
=> "μ™Όμͺ½ 상단(E87)"만 κΈ°λ‘ν•˜λ„λ‘ μˆ˜μ •
=> λ‚˜λ¨Έμ§€ 둜직(μ• λ‘œμ‚¬ν•­/λͺ©ν‘œ, SWOT 4쀄, μž¬λ¬΄λΆ„μ„ 4쀄, μƒκΆŒλΆ„μ„, 사진, 상단 8개 ν•­λͺ©)은 κ±΄λ“œλ¦¬μ§€ μ•ŠμŒ
"""
########## 1) GPT(μ• λ‘œμ‚¬ν•­/λͺ©ν‘œ)
preview_difficulties, preview_goals = analyze_texts(difficulties_input, goals_input)
########## 2) GPT(SWOT)
swot_result = analyze_swot(strengths_input, weaknesses_input, opportunities_input, threats_input)
swot_lines = swot_result.split("\n")
strength_line = next((line for line in swot_lines if line.startswith("강점:")), "")
weakness_line = next((line for line in swot_lines if line.startswith("약점:")), "")
opportunity_line = next((line for line in swot_lines if line.startswith("기회:")), "")
threat_line = next((line for line in swot_lines if line.startswith("μœ„ν˜‘:")), "")
########## 3) GPT(재무)
fin_result = analyze_financials(
avg_monthly_sales, rent, labor_cost, management_cost,
utilities, current_interest, other_cost, cost_ratio
)
fin_lines = fin_result.split("\n")
########## 4) GPT(μƒκΆŒ)
market_result = analyze_market(region_input, industry_input)
########## EXE.xlsx
wb = load_workbook("EXE.xlsx")
ws = wb.active
# (A) 상단 8개
ws.merge_cells("C4:E4")
ws["C4"] = consulting_company_rep
ws["C4"].alignment = Alignment(horizontal="center", vertical="center")
ws.merge_cells("C5:E5")
ws["C5"] = consulting_address
ws["C5"].alignment = Alignment(horizontal="center", vertical="center")
ws.merge_cells("C6:E6")
ws["C6"] = consulting_field
ws["C6"].alignment = Alignment(horizontal="center", vertical="center")
ws.merge_cells("C7:E7")
ws["C7"] = client_name
ws["C7"].alignment = Alignment(horizontal="center", vertical="center")
ws.merge_cells("G4:H4")
ws["G4"] = consulting_contact
ws["G4"].alignment = Alignment(horizontal="center", vertical="center")
ws.merge_cells("G5:H5")
ws["G5"] = consulting_date_time
ws["G5"].alignment = Alignment(horizontal="center", vertical="center")
ws.merge_cells("G6:H6")
ws["G6"] = consulting_round
ws["G6"].alignment = Alignment(horizontal="center", vertical="center")
ws.merge_cells("G7:H7")
ws["G7"] = consulting_region
ws["G7"].alignment = Alignment(horizontal="center", vertical="center")
# (B) μ• λ‘œμ‚¬ν•­/λͺ©ν‘œ
ws["D11"] = preview_difficulties
ws["D22"] = preview_goals
ws["D11"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
ws["D22"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
# (F) 사진
if photo1 is not None:
img1_bytes = BytesIO()
photo1.save(img1_bytes, format="PNG")
img1_bytes.seek(0)
img_for_excel_1 = ExcelImage(img1_bytes)
img_for_excel_1.width = int(37.79 * 7) # κ°€λ‘œ 7cm
img_for_excel_1.height = int(37.79 * 5)
ws.add_image(img_for_excel_1, "B117")
if photo2 is not None:
img2_bytes = BytesIO()
photo2.save(img2_bytes, format="PNG")
img2_bytes.seek(0)
img_for_excel_2 = ExcelImage(img2_bytes)
img_for_excel_2.width = int(37.79 * 7) # κ°€λ‘œ 7cm
img_for_excel_2.height = int(37.79 * 5)
ws.add_image(img_for_excel_2, "F117")
# (C) SWOT κ²°κ³Ό(4μΉΈ)
ws.merge_cells("D45:H45")
ws["D45"] = strength_line
ws["D45"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
ws.merge_cells("D54:H54")
ws["D54"] = weakness_line
ws["D54"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
ws.merge_cells("D63:H63")
ws["D63"] = opportunity_line
ws["D63"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
ws.merge_cells("D72:H72")
ws["D72"] = threat_line
ws["D72"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
########################################################################
# parse_financial_line:
# - 라벨 제거 (콜둠(:) μ•ž 뢀뢄은 λ¬΄μ‹œ)
# - 숫자만 μΆ”μΆœ(문자·기호 λ“± μ „λΆ€ 제거)
# - 3μžλ¦¬λ§ˆλ‹€ 점(.) μ‚½μž…
########################################################################
def parse_financial_line(line: str) -> str:
# 예) "감가상각비: 1234567" -> "1.234.567"
# 예) "순이읡율: 10%" -> "10" (문자·기호 λ“± μ „λΆ€ μ œκ±°ν•˜λ―€λ‘œ %도 버림)
# 1) 콜둠(:) λ’€μ˜ λ‚΄μš© μΆ”μΆœ
parts = line.split(":")
if len(parts) < 2:
return "" # λ³€ν™˜ λΆˆκ°€ μ‹œ 곡백
value_str = parts[1].strip() # 예: "1234567", "10%", "1,234,567원" λ“±
# 2) 숫자만 μΆ”μΆœ (문자·기호 제거)
digits_str = re.sub(r"[^0-9]", "", value_str) # "1234567", "10" λ“±
if not digits_str:
return "" # μˆ«μžκ°€ μ „ν˜€ μ—†μœΌλ©΄ 곡백
# 3) 3μžλ¦¬λ§ˆλ‹€ 점(.) μ‚½μž…
reversed_digits = digits_str[::-1]
grouped = ".".join(reversed_digits[i:i+3] for i in range(0, len(reversed_digits), 3))
final_num = grouped[::-1] # μ •λ°©ν–₯
return final_num
# (D) μž¬λ¬΄λΆ„μ„ κ²°κ³Ό(4쀄 -> E87:E90)
ws.merge_cells("E87:G87")
line0 = fin_lines[0] if len(fin_lines) > 0 else ""
ws["E87"] = parse_financial_line(line0)
ws["E87"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
ws.merge_cells("E88:G88")
line1 = fin_lines[1] if len(fin_lines) > 1 else ""
ws["E88"] = parse_financial_line(line1)
ws["E88"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
ws.merge_cells("E89:G89")
line2 = fin_lines[2] if len(fin_lines) > 2 else ""
ws["E89"] = parse_financial_line(line2)
ws["E89"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
ws.merge_cells("E90:G90")
line3 = fin_lines[3] if len(fin_lines) > 3 else ""
ws["E90"] = parse_financial_line(line3)
ws["E90"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
# (E) μƒκΆŒλΆ„μ„ κ²°κ³Ό(1쀄 -> D101)
ws.merge_cells("D101:H101")
ws["D101"] = market_result
ws["D101"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
################################################################
# (μΆ”κ°€) λ³‘ν•©λœ μ…€μ˜ 'μ™Όμͺ½ 상단'μ—λ§Œ value λŒ€μž… (MergedCell μ—λŸ¬ λ°©μ§€)
#
# 예: E87:F87:G87둜 λ³‘ν•©λœ μƒνƒœλ©΄,
# - E87 = 'λŒ€ν‘œ μ…€'(μ‹€μ œ value κ°€λŠ₯)
# - F87, G87 = MergedCell (read-only)
#
# μ•„λž˜λŠ” μ˜ˆμ‹œλ‘œ E87~E94κΉŒμ§€ 8쀄 μƒμ„±ν•œλ‹€κ³  κ°€μ •
# => 'μ™Όμͺ½ 상단'만 기둝
################################################################
# μ‚¬μš©μž μž…λ ₯ 8개 ν•­λͺ© (예: μ›”ν‰κ· λ§€μΆœμ•‘, μž„μ°¨λ£Œ λ“±)
input_labels = [
"μ›”ν‰κ· λ§€μΆœμ•‘",
"μž„μ°¨λ£Œ",
"인건비",
"관리비",
"μˆ˜λ„κ΄‘μ—΄λΉ„",
"μ§€κΈˆμ΄μž",
"κΈ°νƒ€λΉ„μš©",
"μ›κ°€μœ¨"
]
input_values = [
avg_monthly_sales,
rent,
labor_cost,
management_cost,
utilities,
current_interest,
other_cost,
cost_ratio
]
base_row = 87
for i, (label, value) in enumerate(zip(input_labels, input_values)):
row_idx = base_row + i # 87~94
cell_master = f"E{row_idx}"
ws[cell_master] = f"{label}: {value}"
ws[cell_master].alignment = Alignment(
horizontal="center", vertical="center", wrap_text=True
)
# 예: E95 => 감가상각비(μ˜ˆμ‹œ)
ws["E95"] = "감가상각비(μ˜ˆμ‹œ)"
ws["E95"].alignment = Alignment(
horizontal="center", vertical="center", wrap_text=True
)
# E96~E98 => 순이읡율, μ›”μˆœμˆ˜μ΅, 손읡뢄기점
profit_line = next((l for l in fin_lines if "순이읡율" in l), "")
profit_val = profit_line.split(":")[1].strip() if profit_line else ""
monthly_line = next((l for l in fin_lines if "μ›”μˆœμˆ˜μ΅" in l), "")
monthly_val = monthly_line.split(":")[1].strip() if monthly_line else ""
bep_line = next((l for l in fin_lines if "손읡뢄기점" in l), "")
bep_val = bep_line.split(":")[1].strip() if bep_line else ""
ws["E96"] = f"순이읡율: {profit_val}"
ws["E97"] = f"μ›”μˆœμˆ˜μ΅: {monthly_val}"
ws["E98"] = f"손읡뢄기점: {bep_val}"
for r_ in [96, 97, 98]:
cell_e = f"E{r_}"
ws[cell_e].alignment = Alignment(
horizontal="center", vertical="center", wrap_text=True
)
################################################################
# μ €μž₯
output = BytesIO()
wb.save(output)
output.seek(0)
with tempfile.NamedTemporaryFile(delete=False, suffix=".xlsx") as tmp:
tmp.write(output.getvalue())
tmp.flush()
temp_file_path = tmp.name
final_path = os.path.join(os.path.dirname(temp_file_path), "EXE.xlsx")
os.rename(temp_file_path, final_path)
return final_path
############################
# UI (ν•˜λ‚˜μ˜ λ²„νŠΌλ§Œ 남김)
############################
def main():
with gr.Blocks() as demo:
gr.Markdown("## λͺ¨λ“  μž…λ ₯ ν›„ -> Excel λ‹€μš΄λ‘œλ“œ(ν•˜λ‚˜μ˜ λ²„νŠΌ)")
# (A) 상단 8개
with gr.Row():
consulting_company_rep = gr.Textbox(label="μ»¨μ„€νŒ…μ—…μ²΄λͺ… 및 λŒ€ν‘œ")
consulting_address = gr.Textbox(label="μ»¨μ„€νŒ… μ£Όμ†Œ")
consulting_field = gr.Textbox(label="μ»¨μ„€νŒ… λΆ„μ•Ό")
client_name = gr.Textbox(label="μ„±λͺ…")
with gr.Row():
consulting_contact = gr.Textbox(label="μ»¨μ„€νŒ… 업체 μ—°λ½μ²˜")
consulting_date_time = gr.Textbox(label="μ»¨μ„€νŒ… μˆ˜ν–‰μΌμž 및 μ‹œκ°„")
consulting_round = gr.Textbox(label="μ§„ν–‰μ°¨μˆ˜")
consulting_region = gr.Textbox(label="μ§€μ—­")
gr.Markdown("---")
# (B) μ• λ‘œμ‚¬ν•­/λͺ©ν‘œ
difficulties_input = gr.Textbox(label="μ• λ‘œμ‚¬ν•­ μž…λ ₯", lines=3)
goals_input = gr.Textbox(label="μ»¨μ„€νŒ… λͺ©ν‘œ μž…λ ₯", lines=3)
gr.Markdown("---")
# (C) SWOT
strengths_input = gr.Textbox(label="강점")
weaknesses_input = gr.Textbox(label="약점")
opportunities_input = gr.Textbox(label="기회")
threats_input = gr.Textbox(label="μœ„ν˜‘")
gr.Markdown("---")
# (D) 재무(8개)
with gr.Row():
avg_monthly_sales_input = gr.Textbox(label="μ›”ν‰κ· λ§€μΆœμ•‘")
rent_input = gr.Textbox(label="μž„μ°¨λ£Œ")
labor_cost_input = gr.Textbox(label="인건비")
management_cost_input = gr.Textbox(label="관리비")
with gr.Row():
utilities_input = gr.Textbox(label="μˆ˜λ„κ΄‘μ—΄λΉ„")
current_interest_input = gr.Textbox(label="μ§€κΈˆμ΄μž")
other_cost_input = gr.Textbox(label="κΈ°νƒ€λΉ„μš©")
cost_ratio_input = gr.Textbox(label="μ›κ°€μœ¨")
gr.Markdown("---")
# (E) μƒκΆŒ
region_input = gr.Textbox(label="μ§€μ—­")
industry_input = gr.Textbox(label="μ—…μ’…")
gr.Markdown("---")
# (F) 사진
with gr.Row():
photo_input_1 = gr.Image(label="μˆ˜μ§„μ—…μ²΄μ‚¬μ§„1", type="pil")
photo_input_2 = gr.Image(label="μˆ˜μ§„μ—…μ²΄μ‚¬μ§„2", type="pil")
gr.Markdown("---")
# λ²„νŠΌ
all_in_one_button = gr.Button("λͺ¨λ‘ μž…λ ₯ ν›„, Excel λ‹€μš΄λ‘œλ“œ")
all_in_one_file = gr.File(label="λ‹€μš΄λ‘œλ“œ(클릭 ν›„ μ €μž₯)")
# μ‹€ν–‰
all_in_one_button.click(
fn=run_all_in_one,
inputs=[
# (A)
consulting_company_rep,
consulting_address,
consulting_field,
client_name,
consulting_contact,
consulting_date_time,
consulting_round,
consulting_region,
# (B)
difficulties_input,
goals_input,
# (C)
strengths_input,
weaknesses_input,
opportunities_input,
threats_input,
# (D)
avg_monthly_sales_input,
rent_input,
labor_cost_input,
management_cost_input,
utilities_input,
current_interest_input,
other_cost_input,
cost_ratio_input,
# (E)
region_input,
industry_input,
# (F)
photo_input_1,
photo_input_2,
],
outputs=[all_in_one_file]
)
demo.launch()
if __name__ == "__main__":
main()