Frknrg/RgReport
0
1import streamlit as st2import pandas as pd3import plotly.express as px4import plotly.graph_objects as go5from datetime import datetime, date, timedelta6import db7import pycountry8import json9import os10 11# Page config12st.set_page_config(13 page_title="Sales Report",14 layout="wide",15 initial_sidebar_state="collapsed"16)17 18# --- Security Check ---19# URL'den token parametresini al20query_params = st.query_params21token = query_params.get("token", None)22 23# Güvenlik kontrolü24# Eğer localde çalışıyorsa secrets.toml'dan, sunucuda ise Environment Variable'dan okur25try:26 EXPECTED_TOKEN = st.secrets.get("APP_TOKEN", os.environ.get("APP_TOKEN", "renart-secure-2025-xyz"))27except Exception:28 EXPECTED_TOKEN = os.environ.get("APP_TOKEN", "renart-secure-2025-xyz")29 30if token != EXPECTED_TOKEN:31 st.error("⛔ Erişim Reddedildi: Yetkisiz Giriş")32 st.stop()33 34# Load custom CSS35def load_css():36 st.markdown("""37 <style>38 /* Hide Streamlit Header/Toolbar */39 header[data-testid="stHeader"] {40 visibility: hidden;41 height: 0px;42 }43 /* Hide the decoration at the top */44 /* Hide the decoration at the top */45 .stApp > header {46 display: none;47 }48 49 /* Hide Sidebar Navigation (Page List) */50 [data-testid="stSidebarNav"] {51 display: none;52 }53 54 /* Import fonts */55 @import url('https://fonts.googleapis.com/css2?family=Inter:wght@400;600;700&display=swap');56 57 /* Global Light Theme */58 .stApp {59 background-color: #E4D6C5;60 color: #000000;61 font-family: 'Inter', sans-serif;62 }/* Reduce top padding to move content up */63 .block-container {64 padding-top: 1rem !important;65 padding-bottom: 0rem !important;66 padding-left: 1rem !important;67 padding-right: 1rem !important;68 }69 70 /* Card Styling */71 .css-1r6slb0,72 .css-12w0qpk {73 background-color: #FFFFFF;74 border-radius: 10px;75 padding: 15px;76 box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1);77 }78 79 /* Metric Label */80 .stMetricLabel {81 color: #78898F !important;82 font-size: 14px !important;83 font-weight: 600 !important;84 }85 86 /* Metric Value */87 .stMetricValue {88 color: #984216 !important;89 font-size: 36px !important;90 font-weight: bold !important;91 }92 93 /* Custom Card Class (for HTML injection) */94 .custom-card {95 background-color: #E4D6C5;96 border: 2px solid #984216;97 border-radius: 8px;98 padding: 20px;99 margin-bottom: 20px;100 color: #000000;101 box-shadow: none;102 height: 110%;103 }104 105 .card-title {106 color: #78898F;107 font-size: 14px;108 font-weight: 600;109 margin-bottom: 5px;110 }111 112 /* Popover Styling */113 div[data-testid="stPopoverBody"] {114 background-color: #FDFBF7;115 border: 1px solid #984216;116 border-radius: 10px;117 }118 119 /* Button Styling in Popover */120 div[data-testid="stPopoverBody"] button {121 background-color: #FFFFFF;122 border: 1px solid #E0E0E0;123 color: #000000;124 }125 div[data-testid="stPopoverBody"] button:hover {126 border-color: #984216;127 color: #984216;128 }129 font-size: 18px;130 font-weight: bold;131 margin-bottom: 10px;132 }133 134 /* Chart Container */135 .chart-container {136 background-color: #FFFFFF;137 border-radius: 8px;138 padding: 15px;139 box-shadow: 0 2px 4px rgba(0,0,0,0.05);140 }141 142 /* Tabs Styling */143 .stTabs [data-baseweb="tab-list"] {144 gap: 10px;145 background-color: transparent;146 }147 .stTabs [data-baseweb="tab"] {148 height: 40px;149 white-space: pre-wrap;150 background-color: #FFFFFF;151 border-radius: 5px;152 color: #000000;153 font-weight: 600;154 border: 1px solid #E0E0E0;155 padding: 0 20px;156 }157 .stTabs [aria-selected="true"] {158 background-color: #984216 !important;159 color: #FFFFFF !important;160 border-color: #984216 !important;161 }162 163 /* Expander Styling */164 .streamlit-expanderHeader {165 background-color: #FFFFFF;166 border-radius: 5px;167 color: #000000;168 font-weight: 600;169 }170 171 /* Button Styling */172 .stButton > button {173 background-color: #984216;174 color: #FFFFFF;175 border: none;176 border-radius: 5px;177 font-weight: 600;178 padding: 0.5rem 1rem;179 }180 .stButton > button:hover {181 background-color: #7a3310;182 color: #FFFFFF;183 }184 185 /* Popover Button Styling */186 /* Target the button element that triggers the popover */187 button[data-testid="stPopoverButton"] {188 color: #984216 !important; /* Requested Rust Color */189 border-color: #984216 !important;190 background-color: transparent !important;191 }192 193 /* Target ALL content inside the button */194 /* Target ALL content inside the button */195 /* Target ALL content inside the button */196 button[data-testid="stPopoverButton"] * {197 color: #984216 !important; /* Requested Rust Color */198 /* Removed aggressive fill on * to avoid coloring SVG background rects */199 background-color: transparent !important; /* Remove any background from children */200 border: none !important; /* Remove borders from children */201 box-shadow: none !important; /* Remove shadows */202 }203 204 /* Target SVG paths specifically for the icon */205 /* The first path is a bounding box with fill="none", we must ensure it stays transparent */206 button[data-testid="stPopoverButton"] svg path[fill="none"] {207 fill: transparent !important;208 }209 210 /* The second path is the arrow, we want to color this one */211 button[data-testid="stPopoverButton"] svg path:not([fill="none"]) {212 fill: #984216 !important;213 }214 215 /* Custom Bar Chart Styles */216 .bar-container {217 background-color: #78898F;218 width: 100%;219 height: 30px;220 position: relative;221 display: flex;222 align-items: center;223 border-radius: 4px;224 }225 .bar-filled {226 background-color: #984216;227 height: 100%;228 display: flex;229 align-items: center;230 border-radius: 4px 0 0 4px;231 }232 .bar-value {233 position: absolute;234 right: 8px;235 color: white;236 font-size: 14px;237 font-weight: normal;238 }239 .bar-pct {240 margin-left: 8px;241 color: white;242 font-size: 14px;243 font-weight: normal;244 white-space: nowrap;245 }246 247 /* Target pseudo-elements which might hold the arrow background */248 button[data-testid="stPopoverButton"]::after,249 button[data-testid="stPopoverButton"]::before,250 button[data-testid="stPopoverButton"] > div::after,251 button[data-testid="stPopoverButton"] > div::before {252 background-color: transparent !important;253 }254 255 /* Hover State */256 button[data-testid="stPopoverButton"]:hover {257 background-color: #D4C6B5 !important; /* Slightly darker beige */258 border-color: #984216 !important;259 }260 261 /* Ensure text stays Rust color on hover */262 button[data-testid="stPopoverButton"]:hover * {263 color: #984216 !important;264 }265 266 /* --- ANALYZE BUTTON STYLING (Primary Button) --- */267 /* Target the primary button specifically */268 button[kind="primary"] {269 background-color: #E4D6C5 !important; /* Beige background */270 color: #984216 !important; /* Rust text */271 border: 1px solid #984216 !important; /* Rust border */272 box-shadow: none !important;273 margin-top: 2rem !important; /* Align with Filters button */274 height: 42px !important; /* Fixed height for alignment */275 }276 button[kind="primary"]:hover {277 background-color: #d4c6b5 !important; /* Slightly darker beige on hover */278 color: #984216 !important;279 border: 1px solid #984216 !important;280 }281 282 /* Ensure the focus/active state also keeps the color */283 button[kind="primary"]:active, 284 button[kind="primary"]:focus {285 background-color: #E4D6C5 !important;286 color: #984216 !important;287 border: 1px solid #984216 !important;288 }289 290 /* --- SECONDARY BUTTON STYLING (Clear Filters) --- */291 button[kind="secondary"] {292 background-color: #E4D6C5 !important;293 color: #984216 !important;294 border: 1px solid #984216 !important;295 box-shadow: none !important;296 margin-top: 2rem !important; /* Align with Filters button */297 height: 42px !important; /* Fixed height for alignment */298 }299 button[kind="secondary"]:hover {300 background-color: #d4c6b5 !important;301 color: #984216 !important;302 border-color: #984216 !important;303 }304 305 /* --- POPOVER BUTTON STYLING --- */306 /* Target the button element that triggers the popover */307 button[data-testid="stPopoverButton"] {308 color: #984216 !important; /* Requested Rust Color */309 border-color: #984216 !important;310 background-color: transparent !important;311 margin-top: 2rem !important; /* Align with other buttons */312 height: 42px !important; /* Fixed height for alignment */313 }314 315 /* Target ALL content inside the button */316 button[data-testid="stPopoverButton"] * {317 color: #984216 !important; /* Requested Rust Color */318 background-color: transparent !important; 319 border: none !important; 320 box-shadow: none !important; 321 }322 323 /* Target SVG paths specifically for the icon */324 button[data-testid="stPopoverButton"] svg path[fill="none"] {325 fill: transparent !important;326 }327 328 button[data-testid="stPopoverButton"] svg path:not([fill="none"]) {329 fill: #984216 !important;330 }331 332 /* Custom Bar Chart Styles */333 .bar-container {334 background-color: #78898F;335 width: 100%;336 height: 30px;337 position: relative;338 display: flex;339 align-items: center;340 border-radius: 4px;341 }342 .bar-filled {343 background-color: #984216;344 height: 100%;345 display: flex;346 align-items: center;347 border-radius: 4px 0 0 4px;348 }349 .bar-value {350 position: absolute;351 right: 8px;352 color: white;353 font-size: 14px;354 font-weight: normal;355 }356 .bar-pct {357 margin-left: 8px;358 color: white;359 font-size: 14px;360 font-weight: normal;361 white-space: nowrap;362 }363 364 /* Target pseudo-elements which might hold the arrow background */365 button[data-testid="stPopoverButton"]::after,366 button[data-testid="stPopoverButton"]::before,367 button[data-testid="stPopoverButton"] > div::after,368 button[data-testid="stPopoverButton"] > div::before {369 background-color: transparent !important;370 }371 372 /* Hover State */373 button[data-testid="stPopoverButton"]:hover {374 background-color: #D4C6B5 !important; /* Slightly darker beige */375 border-color: #984216 !important;376 }377 378 /* Ensure text stays Rust color on hover */379 button[data-testid="stPopoverButton"]:hover * {380 color: #984216 !important;381 }382 383 /* Style for st.container(border=True) to match custom-card */384 div[data-testid="stVerticalBlockBorderWrapper"],385 div[class*="stVerticalBlockBorderWrapper"] {386 border: 2px solid #984216 !important;387 background-color: #E4D6C5 !important;388 border-radius: 10px !important;389 padding: 15px !important;390 box-shadow: none !important;391 }392 393 /* --- Dialog/Modal Styling (NUCLEAR OPTION) --- */394 [data-testid="stDialog"] {395 --text-color: #000000 !important;396 }397 398 [data-testid="stDialog"] > div[role="dialog"] {399 background-color: #E4D6C5 !important;400 color: #000000 !important;401 border: 2px solid #984216 !important;402 border-radius: 10px !important;403 }404 405 /* Target EVERYTHING inside the dialog */406 [data-testid="stDialog"] * {407 color: #000000 !important;408 }409 410 /* Specific overrides for Streamlit components that might resist */411 [data-testid="stDialog"] .stMarkdown,412 [data-testid="stDialog"] .stMarkdown p,413 [data-testid="stDialog"] h1,414 [data-testid="stDialog"] h2,415 [data-testid="stDialog"] h3,416 [data-testid="stDialog"] h4,417 [data-testid="stDialog"] span,418 [data-testid="stDialog"] div,419 [data-testid="stDialog"] label,420 [data-testid="stDialog"] li,421 [data-testid="stDialog"] button {422 color: #000000 !important;423 }424 425 /* Close button specific */426 [data-testid="stDialog"] button[aria-label="Close"] {427 color: #984216 !important;428 }429 /* --- POPOVER & WIDGET STYLING (Aggressive Fix) --- */430 431 /* 1. Popover Container — target all possible wrapper divs */432 div[data-testid="stPopoverBody"],433 div[data-testid="stPopoverBody"] > div,434 [data-testid="stPopover"],435 [class*="popover"],436 [data-baseweb="popover"],437 [data-baseweb="popover"] > div,438 [data-baseweb="popover"] [data-testid="stVerticalBlock"] {439 background-color: #E4D6C5 !important;440 color: #000000 !important;441 }442 443 div[data-testid="stPopoverBody"] {444 border: 2px solid #984216 !important;445 border-radius: 10px !important;446 padding: 15px !important;447 box-shadow: 0 4px 20px rgba(152,66,22,0.15) !important;448 }449 450 /* 2. Force ALL Text Color in Popover */451 div[data-testid="stPopoverBody"] *,452 [data-baseweb="popover"] * {453 color: #000000 !important;454 }455 456 /* 3. Radio Buttons — restore visibility */457 div[data-testid="stPopoverBody"] input[type="radio"] {458 accent-color: #984216 !important;459 width: 16px !important;460 height: 16px !important;461 opacity: 1 !important;462 visibility: visible !important;463 }464 div[data-testid="stPopoverBody"] [role="radio"] {465 border: 2px solid #984216 !important;466 background-color: #FDFBF7 !important;467 }468 div[data-testid="stPopoverBody"] [role="radio"][aria-checked="true"] {469 background-color: #984216 !important;470 border-color: #984216 !important;471 }472 473 /* 3. Radio Buttons */474 div[data-testid="stPopoverBody"] div[role="radiogroup"] label {475 background-color: transparent !important;476 color: #000000 !important;477 }478 div[data-testid="stPopoverBody"] div[role="radiogroup"] label p {479 color: #000000 !important;480 font-weight: 500 !important;481 }482 483 /* 4. Selectbox & Multiselect Containers */484 div[data-testid="stPopoverBody"] div[data-baseweb="select"] > div,485 [data-baseweb="popover"] div[data-baseweb="select"] > div {486 background-color: #FDFBF7 !important;487 border-color: #984216 !important;488 color: #000000 !important;489 }490 491 /* 5. Dropdown Options (When opened) */492 ul[data-testid="stSelectboxVirtualDropdown"],493 ul[data-testid="stMultiSelectVirtualDropdown"],494 ul[data-testid="stSelectboxVirtualDropdown"] > div,495 ul[data-testid="stMultiSelectVirtualDropdown"] > div,496 li[role="option"] {497 background-color: #E4D6C5 !important;498 color: #000000 !important;499 }500 li[role="option"]:hover {501 background-color: #d4c6b5 !important;502 }503 504 /* 6. Input Fields inside multiselect — hide inner rectangle */505 div[data-testid="stPopoverBody"] input {506 background-color: transparent !important;507 color: #000000 !important;508 border: none !important;509 outline: none !important;510 box-shadow: none !important;511 }512 /* Multiselect inner search input specifically */513 div[data-testid="stPopoverBody"] div[data-baseweb="select"] input,514 div[data-testid="stPopoverBody"] [data-baseweb="input"] input {515 border: none !important;516 outline: none !important;517 box-shadow: none !important;518 background: transparent !important;519 }520 521 /* 7. Buttons inside Popover */522 div[data-testid="stPopoverBody"] button {523 background-color: #FDFBF7 !important;524 border: 1px solid #984216 !important;525 color: #000000 !important;526 }527 div[data-testid="stPopoverBody"] button:hover {528 border-color: #984216 !important;529 color: #984216 !important;530 background-color: #d4c6b5 !important;531 }532 533 /* 8. Headers inside Popover */534 div[data-testid="stPopoverBody"] h1,535 div[data-testid="stPopoverBody"] h2,536 div[data-testid="stPopoverBody"] h3,537 div[data-testid="stPopoverBody"] h4 {538 color: #984216 !important;539 }540 541 /* 9. Caption & small text */542 div[data-testid="stPopoverBody"] small,543 div[data-testid="stPopoverBody"] [data-testid="stCaptionContainer"] {544 color: #555555 !important;545 }546 547 /* 10. Markdown text in popover */548 div[data-testid="stPopoverBody"] p,549 div[data-testid="stPopoverBody"] label,550 div[data-testid="stPopoverBody"] span {551 color: #000000 !important;552 }553 554 </style>555 """, unsafe_allow_html=True)556 557load_css()558 559 560 561 562# --- State Management ---563# Handle pending updates from chart interactions564if "pending_channel_update" in st.session_state:565 st.session_state.channels = st.session_state.pending_channel_update566 del st.session_state.pending_channel_update567 568if "chart_reset_id" not in st.session_state:569 st.session_state.chart_reset_id = 0570 571# --- Header & Filters ---572header_col, analyze_col, clear_col, export_col, filter_col = st.columns([4, 1, 1, 1, 1]) 573 574# Create placeholder for Export button to be populated later575with export_col:576 export_placeholder = st.empty()577 578with header_col:579 pass # Header text removed as requested580 581# --- Analyze Button ---582# Render button immediately to prevent layout shifts583analyze_clicked = False584with analyze_col:585 if st.button("Analyze", key="analyze_btn", type="primary", width="stretch"):586 analyze_clicked = True587 588# --- Clear Filters Button ---589with clear_col:590 if st.button("Clear Filters", key="clear_filters_btn", type="secondary", width="stretch"):591 # Reset session state variables592 st.session_state.channels = []593 st.session_state.country_filter = []594 st.session_state.date_filter_select = "This Year"595 st.rerun()596 597# --- Export Button (Placeholder or Real) ---598# Export button logic moved to after data loading599 600# --- Filters (Popover) ---601with filter_col:602 with st.popover("Filters ≡", width="stretch"):603 st.markdown("### Filter Options")604 605 # Date Filter (Presets)606 st.markdown("**Tarih Aralığı**")607 608 if 'sales_date_preset' not in st.session_state:609 st.session_state.sales_date_preset = "This Year"610 611 preset_options = {612 "Bugün": "Today",613 "Dün": "Yesterday",614 "Son 7 Gün": "Last 7 Days",615 "Son 30 Gün": "Last 30 Days",616 "Son 90 Gün": "Last 90 Days",617 "Bu Ay": "This Month",618 "Geçen Ay": "Last Month",619 "Bu Yıl": "This Year",620 "Tüm Zamanlar": "All Time",621 "Özel Aralık": "Custom Range"622 }623 624 # Reverse mapping for logic625 preset_map = {v: k for k, v in preset_options.items()}626 627 selected_preset_label = st.radio(628 "Hızlı Seçim",629 options=list(preset_options.keys()),630 index=list(preset_options.keys()).index(preset_map.get(st.session_state.sales_date_preset, "Bu Yıl")),631 key="temp_sales_preset",632 label_visibility="collapsed"633 )634 635 # Update session state636 st.session_state.sales_date_preset = preset_options[selected_preset_label]637 selected_date_filter = st.session_state.sales_date_preset638 639 # Calculate dates640 today = datetime.now().date()641 642 if selected_date_filter == "Custom Range":643 st.divider()644 col_d1, col_d2 = st.columns(2)645 with col_d1:646 start_date = st.date_input("Başlangıç", today - timedelta(days=30), key="custom_start")647 with col_d2:648 end_date = st.date_input("Bitiş", today, key="custom_end")649 elif selected_date_filter == "This Month":650 start_date = today.replace(day=1)651 end_date = today652 elif selected_date_filter == "Last Month":653 last_month_end = today.replace(day=1) - timedelta(days=1)654 start_date = last_month_end.replace(day=1)655 end_date = last_month_end656 elif selected_date_filter == "This Year":657 start_date = today.replace(month=1, day=1)658 end_date = today659 elif selected_date_filter == "All Time":660 start_date = date(2020, 1, 1)661 end_date = today662 elif selected_date_filter == "Today":663 start_date = today664 end_date = today665 elif selected_date_filter == "Yesterday":666 start_date = today - timedelta(days=1)667 end_date = start_date668 elif selected_date_filter == "Last 7 Days":669 start_date = today - timedelta(days=7)670 end_date = today671 elif selected_date_filter == "Last 30 Days":672 start_date = today - timedelta(days=30)673 end_date = today674 elif selected_date_filter == "Last 90 Days":675 start_date = today - timedelta(days=90)676 end_date = today677 else:678 # Fallback679 start_date = today.replace(month=1, day=1)680 end_date = today681 682 st.caption(f"Seçili: {start_date.strftime('%d.%m.%Y')} - {end_date.strftime('%d.%m.%Y')}")683 684 # Country Filter685 filter_options = db.get_filter_options()686 all_countries = filter_options.get('countries', [])687 688 selected_countries = st.multiselect(689 "Countries",690 options=all_countries,691 default=[],692 key="country_filter"693 )694 695 # Channel Filter696 all_channels = ['Eternate', 'Vianisa', 'Market Place']697 698 # Check if we need to update channels from session state (chart interaction)699 if "channels" not in st.session_state:700 st.session_state.channels = []701 702 selected_channels = st.multiselect(703 "Channels",704 options=all_channels,705 default=st.session_state.channels,706 key="channel_filter"707 )708 709 # Update session state if changed in filter710 if selected_channels != st.session_state.channels:711 st.session_state.channels = selected_channels712 713# --- Date Validation ---714if start_date > end_date:715 st.error("Error: 'Date From' cannot be after 'Date To'. Please select a valid date range.")716 st.stop()717 718# --- Data Fetching ---719# Convert dates to string for SQL720s_date = start_date.strftime("%Y-%m-%d")721e_date = end_date.strftime("%Y-%m-%d")722 723metrics = db.get_metrics(s_date, e_date, selected_countries, selected_channels)724monthly_data = db.get_monthly_data(s_date, e_date, selected_countries, selected_channels)725 726# Export button logic is handled at the end of the file727# For the channel list/chart, we want to show ALL channels even if one is selected, 728# so we can switch between them. Passing [] for channels ignores the channel filter.729channel_data = db.get_channel_data(s_date, e_date, selected_countries, []) 730product_data = db.get_product_data(s_date, e_date, selected_countries, selected_channels)731if not product_data.empty:732 product_data['product_code'] = product_data['product_code'].fillna("Unknown")733map_data = db.get_map_data(s_date, e_date, selected_countries, selected_channels)734 735# Initialize figures dictionary for export736figures = {}737 738# Debug Info739# st.toast(f"Data Loaded: {not metrics.empty}, Sales: {metrics['total_sales'].iloc[0] if not metrics.empty else 0}")740 741 742# --- Helper Functions ---743def format_currency(val):744 if val >= 1000000:745 return f"${val/1000000:.1f}M".replace('.', ',')746 elif val >= 1000:747 return f"${val/1000:.1f}K".replace('.', ',')748 else:749 return f"${val:,.0f}".replace(',', '.') # Swap comma to dot for thousands if needed, but usually currency uses comma for decimals in TR locale. 750 # User asked: "fiyatı virgülle ($5,8M) adeti arasında nokta ile (19.812)".751 # So for millions/thousands: 5.8M -> 5,8M.752 # For raw numbers: 1,000 -> 1.000.753 754def format_number(val):755 return f"{val:,.0f}".replace(',', '.')756 757@st.dialog("Product Details")758def show_product_image(product_code, img_url):759 st.markdown(f"<h3 style='text-align: center;'>{product_code}</h3>", unsafe_allow_html=True)760 if img_url:761 # Create columns to center the image if needed, but st.image usually handles it.762 # Removing use_container_width and setting a fixed width might help if container is too small.763 # But usually use_container_width fills the dialog.764 # Let's try to force a large width.765 st.image(img_url, caption=product_code, width=300) 766 else:767 st.info("No image available")768 769@st.dialog("Product Analysis (Top 5)", width="large")770def show_product_analysis(details_df, location_df, history_df, metal_df):771 if details_df.empty:772 st.warning("No detailed data available for analysis.")773 return774 775 # --- Parsing Logic ---776 import re777 778 # Extract Price779 details_df['price'] = details_df['item'].apply(lambda x: float(x.get('unitPrice', 0)))780 781 # Metal counts are now passed directly as metal_df782 metal_counts = metal_df783 784 # --- Visualizations ---785 786 # 1. Product Showcase (Images & Names)787 st.markdown("### Top 5 Products")788 789 # Create columns for products (up to 5)790 num_products = min(len(details_df), 5)791 cols = st.columns(num_products)792 793 for i, col in enumerate(cols):794 row = details_df.iloc[i]795 item = row['item']796 img_url = item.get('imageUrl')797 # Use product_code (SKU) instead of name798 product_code = row['product_code']799 800 with col:801 if img_url:802 # Use HTML to make the image clickable and open in a new tab803 st.markdown(f'''804 <a href="{img_url}" target="_blank">805 <img src="{img_url}" style="width:100%; border-radius: 5px;">806 </a>807 ''', unsafe_allow_html=True)808 else:809 st.write("📷 No Image")810 811 st.caption(product_code)812 813 st.markdown("---")814 st.markdown("### Insights")815 816 col1, col2 = st.columns(2)817 818 with col1:819 st.markdown("**Metal Preference**")820 if not metal_counts.empty:821 fig_metal = px.pie(metal_counts, values='Count', names='Metal', 822 color_discrete_sequence=px.colors.sequential.RdBu,823 hole=0.4)824 fig_metal.update_layout(margin=dict(l=0, r=0, t=0, b=0), height=200)825 st.plotly_chart(fig_metal, use_container_width=True)826 else:827 st.info("No metal data found.")828 829 with col2:830 st.markdown("**Unit Price ($)**")831 if not details_df.empty:832 # Create a bar chart for prices833 # Use product_code as label834 fig_price = px.bar(details_df, x='price', y='product_code', orientation='h',835 text='price')836 fig_price.update_traces(marker_color='#984216', texttemplate='$%{text:,.0f}', textposition='inside')837 fig_price.update_layout(838 plot_bgcolor='rgba(0,0,0,0)',839 paper_bgcolor='rgba(0,0,0,0)',840 xaxis=dict(showgrid=False, showticklabels=False, title=None),841 yaxis=dict(showgrid=False, title=None, categoryorder='total ascending'),842 margin=dict(l=0, r=0, t=0, b=0),843 height=200844 )845 st.plotly_chart(fig_price, use_container_width=True)846 else:847 st.info("No price data found.")848 849 st.markdown("---")850 st.markdown("**Top Locations (Country - State)**")851 if not location_df.empty:852 # Create label "Country - State"853 location_df['label'] = location_df['country'] + " - " + location_df['state'].fillna('')854 855 fig_loc = px.bar(location_df.head(10), x='order_count', y='label', orientation='h',856 text='order_count')857 fig_loc.update_traces(marker_color='#984216', textposition='inside', textfont_color='white')858 fig_loc.update_layout(859 plot_bgcolor='rgba(0,0,0,0)',860 paper_bgcolor='rgba(0,0,0,0)',861 xaxis=dict(showgrid=False, showticklabels=False, title=None),862 yaxis=dict(showgrid=False, categoryorder='total ascending', title=None),863 margin=dict(l=0, r=0, t=0, b=0),864 height=300865 )866 st.plotly_chart(fig_loc, use_container_width=True)867 else:868 st.info("No location data found.")869 870 # --- Marketing Analysis ---871 st.markdown("---")872 st.markdown("### Marketing Insights")873 874 if not history_df.empty:875 # Convert order_date to datetime876 history_df['order_date'] = pd.to_datetime(history_df['order_date'])877 878 m_col1, m_col2 = st.columns(2)879 880 with m_col1:881 st.markdown("**Peak Shopping Times (Hour of Day)**")882 # Extract hour883 history_df['hour'] = history_df['order_date'].dt.hour884 hourly_counts = history_df['hour'].value_counts().sort_index().reset_index()885 hourly_counts.columns = ['Hour', 'Orders']886 887 fig_hour = px.bar(hourly_counts, x='Hour', y='Orders')888 fig_hour.update_traces(marker_color='#984216')889 fig_hour.update_layout(890 plot_bgcolor='rgba(0,0,0,0)',891 paper_bgcolor='rgba(0,0,0,0)',892 xaxis=dict(tickmode='linear', tick0=0, dtick=2),893 margin=dict(l=0, r=0, t=0, b=0),894 height=250895 )896 st.plotly_chart(fig_hour, use_container_width=True)897 898 with m_col2:899 st.markdown("**Sales Trend (Daily)**")900 # Resample to daily901 daily_trend = history_df.set_index('order_date').resample('D').size().reset_index(name='Orders')902 903 fig_trend = px.line(daily_trend, x='order_date', y='Orders')904 fig_trend.update_traces(line_color='#984216', line_width=2)905 fig_trend.update_layout(906 plot_bgcolor='rgba(0,0,0,0)',907 paper_bgcolor='rgba(0,0,0,0)',908 xaxis_title=None,909 yaxis_title=None,910 margin=dict(l=0, r=0, t=0, b=0),911 height=250912 )913 st.plotly_chart(fig_trend, use_container_width=True)914 else:915 st.info("No history data available for marketing insights.")916 917# --- Analyze Button Logic ---918if analyze_clicked:919 if not product_data.empty:920 # Calculate top products here for the analysis921 top_products_analysis = product_data.head(5).copy()922 top_codes = top_products_analysis['product_code'].tolist()923 924 # Fetch detailed data925 details = db.get_product_details(top_codes, s_date, e_date, selected_countries, selected_channels)926 # Fetch location stats927 loc_stats = db.get_product_location_stats(top_codes, s_date, e_date, selected_countries, selected_channels)928 # Fetch sales history for marketing charts929 history = db.get_product_sales_history(top_codes, s_date, e_date, selected_countries, selected_channels)930 # Fetch metal stats for ALL products931 metal_stats = db.get_metal_stats(s_date, e_date, selected_countries, selected_channels)932 933 # Sort details to match the order of top_codes (Sales Descending)934 if not details.empty:935 # Create a categorical type with the order of top_codes936 details['product_code'] = pd.Categorical(details['product_code'], categories=top_codes, ordered=True)937 details = details.sort_values('product_code')938 939 show_product_analysis(details, loc_stats, history, metal_stats)940 else:941 st.toast("No products to analyze.")942 943 944 945 946# --- Row 1: Sales ---947st.markdown("---")948row1_col1, row1_col2, row1_col3, row1_col4 = st.columns([1.5, 2, 1.5, 1.5])949 950# KPI Card: Total Sales951total_sales_gross = metrics['total_sales'].iloc[0] if not metrics.empty and metrics['total_sales'].iloc[0] else 0952total_returns = db.get_total_returns(s_date, e_date)953net_sales = total_sales_gross - total_returns954 955# Alias for compatibility with charts (Charts use Gross Sales)956total_sales = total_sales_gross957 958aov = metrics['aov'].iloc[0] if not metrics.empty and metrics['aov'].iloc[0] else 0959 960# Calculate Previous Year Metrics (Same Period Last Year)961try:962 py_s_date = start_date.replace(year=start_date.year - 1)963 py_e_date = end_date.replace(year=end_date.year - 1)964except ValueError: # Handle leap year Feb 29965 py_s_date = start_date.replace(year=start_date.year - 1, day=28)966 py_e_date = end_date.replace(year=end_date.year - 1, day=28)967 968py_s_str = py_s_date.strftime("%Y-%m-%d")969py_e_str = py_e_date.strftime("%Y-%m-%d")970 971py_metrics = db.get_metrics(py_s_str, py_e_str, selected_countries, selected_channels)972py_sales_gross = py_metrics['total_sales'].iloc[0] if not py_metrics.empty and py_metrics['total_sales'].iloc[0] else 0973py_returns = db.get_total_returns(py_s_str, py_e_str)974py_net_sales = py_sales_gross - py_returns975 976# Calculate Percentage Change based on Net Sales977if py_net_sales > 0:978 sales_pct_change = ((net_sales - py_net_sales) / py_net_sales) * 100979else:980 sales_pct_change = 0 if net_sales == 0 else 100981 982# Formatting983sales_sign = "+" if sales_pct_change > 0 else ""984sales_pct_str = f"{sales_sign}{sales_pct_change:.1f}%"985sales_color = "#3F9119" if sales_pct_change > 0 else "#984216"986 987# Hardcoded Target (as requested)988sales_target = 25000000 989 990progress = min(net_sales / sales_target * 100, 100)991 992with row1_col1:993 st.markdown(f"""994 <div class="custom-card">995 <div class="card-title">Net Sales</div>996 <div style="display: flex; align-items: baseline; gap: 10px;">997 <div class="big-number">{format_currency(net_sales)}</div>998 <div style="color: #984216; font-size: 20px; font-weight: bold;">AOV ${format_number(aov)}</div>999 </div>1000 <div class="sub-text" style="color: {sales_color}; font-size: 16px; font-weight: bold; margin-top: 5px; margin-bottom: 5px;">1001 {sales_pct_str} <span style="color: #555; font-size: 14px; font-weight: normal;">(vs. Last Year: {format_currency(py_net_sales)})</span>1002 </div>1003 <div style="font-size: 12px; color: #666; margin-bottom: 10px;">1004 Gross: {format_currency(total_sales_gross)} | Returns: <span style="color: #984216;">-{format_currency(total_returns)}</span>1005 </div>1006 <div>1007 <div style="display: flex; justify-content: space-between; margin-bottom: 5px; color: #000000; font-size: 14px;">1008 <span>Target: {format_currency(sales_target)}</span>1009 <span style="font-weight: bold; color: #984216;">%{progress:.1f}</span>1010 </div>1011 <div style="background-color: #78898F; height: 13px; border-radius: 6px; width: 100%;">1012 <div style="background-color: #984216; height: 13px; border-radius: 6px; width: {progress:.1f}%;"></div>1013 </div>1014 </div>1015 </div>1016 """, unsafe_allow_html=True)1017 1018 1019 1020# Chart: Monthly Sales1021with row1_col2:1022 st.markdown('<div class="card-title">Sales Trend</div>', unsafe_allow_html=True)1023 if not monthly_data.empty:1024 fig = px.bar(monthly_data, x='month', y='sales')1025 fig.update_layout(1026 paper_bgcolor='rgba(0,0,0,0)',1027 plot_bgcolor='rgba(0,0,0,0)',1028 font_color='black',1029 xaxis=dict(showgrid=False, tickfont=dict(color='#78898F'), title_font=dict(color='#78898F'), fixedrange=True),1030 yaxis=dict(showgrid=True, gridcolor='#DDDDDD', tickfont=dict(color='#78898F'), title_font=dict(color='#78898F'), fixedrange=True),1031 margin=dict(l=0, r=0, t=0, b=0),1032 height=250,1033 dragmode=False1034 )1035 fig.update_traces(marker_color='#984216')1036 st.plotly_chart(fig, use_container_width=True, config={'displayModeBar': False})1037 figures['Sales Trend'] = fig1038 1039 1040 else:1041 st.info("No data for chart")1042 1043# List: Sales by Channel1044with row1_col3:1045 st.markdown('<div class="card-title">By Channel</div>', unsafe_allow_html=True)1046 if not channel_data.empty:1047 # Calculate percentages for the background bar effect1048 channel_data['pct'] = channel_data['sales'] / total_sales1049 channel_data['sales_formatted'] = channel_data['sales'].apply(format_currency)1050 channel_data['pct_formatted'] = (channel_data['pct'] * 100).map('{:.1f}%'.format)1051 1052 # Reverse order for Plotly (so highest is at top)1053 channel_data = channel_data.iloc[::-1]1054 1055 # Create a custom hover text or display text1056 channel_data['display_text'] = channel_data['pct_formatted'] + " " + channel_data['sales_formatted']1057 1058 # Plotly Horizontal Bar1059 fig = go.Figure()1060 1061 # Background Bar (100% width relative to max or total? Design looks like 100% width of the container)1062 # To mimic the design (progress bar style), we usually define a max range.1063 # Let's assume the max sales is the reference for the bar width or just use the container width.1064 # Actually, the design shows a progress bar relative to Total Sales.1065 1066 # We will use a stacked bar approach or just one bar if we want simple selection.1067 # To get the "Grey background" effect, we can add a trace of "Remaining" but that's complex for click events.1068 # Simpler: Just the Rust bar. The user asked for the grey background in the previous step, so we should try to keep it.1069 # Trace 1: Background (Total Sales or Max) - Color #78898F1070 # Trace 2: Actual Sales - Color #9842161071 1072 # For interactivity, we only need to click the row.1073 1074 fig.add_trace(go.Bar(1075 y=channel_data['channel'],1076 x=[total_sales] * len(channel_data), # Full width background1077 orientation='h',1078 marker_color='#78898F',1079 hoverinfo='skip',1080 showlegend=False,1081 width=0.4, # Thicker bars1082 text=channel_data['sales_formatted'], # Value on the right1083 textposition='inside',1084 insidetextanchor='end',1085 textfont=dict(color='white', size=14)1086 ))1087 1088 # Determine colors based on selection1089 # If no selection (or all selected), all Rust.1090 # If selection exists, selected is Rust, others are Grey/Opacity.1091 1092 current_selection = st.session_state.get("channels", [])1093 1094 colors = []1095 for c in channel_data['channel']:1096 if not current_selection: # No filter active1097 colors.append('#984216')1098 elif c in current_selection: # Selected1099 colors.append('#984216')1100 else: # Not selected1101 colors.append('rgba(152, 66, 22, 0.3)') # Rust with low opacity1102 1103 fig.add_trace(go.Bar(1104 y=channel_data['channel'],1105 x=channel_data['sales'],1106 orientation='h',1107 marker_color=colors,1108 text=channel_data['pct_formatted'], # Percentage on the left (now outside/right of filled bar)1109 textposition='outside', 1110 textfont=dict(color='white', size=14), # White text to contrast with grey background1111 hoverinfo='y+x',1112 showlegend=False,1113 width=0.41114 ))1115 1116 fig.update_layout(1117 barmode='overlay',1118 paper_bgcolor='rgba(0,0,0,0)',1119 plot_bgcolor='rgba(0,0,0,0)',1120 font_color='black',1121 xaxis=dict(showgrid=False, showticklabels=False, range=[0, total_sales], fixedrange=True),1122 yaxis=dict(showgrid=False, showticklabels=True, tickfont=dict(size=14, color='black'), fixedrange=True),1123 margin=dict(l=0, r=0, t=0, b=0),1124 height=150,1125 dragmode=False1126 )1127 1128 st.plotly_chart(fig, use_container_width=True, config={'displayModeBar': False})1129 figures['Sales By Channel'] = fig1130 1131# List: Sales by Product1132with row1_col4:1133 # Title moved inside the column structure below1134 if not product_data.empty:1135 # Prepare data for Plotly1136 top_products = product_data.head(5).copy()1137 top_products['pct'] = top_products['sales'] / total_sales1138 top_products['sales_formatted'] = top_products['sales'].apply(format_currency)1139 top_products['pct_formatted'] = (top_products['pct'] * 100).map('{:.1f}%'.format)1140 top_products['display_text'] = top_products['pct_formatted'] + " " + top_products['sales_formatted']1141 else:1142 top_products = pd.DataFrame(columns=['product_code', 'sales', 'pct', 'sales_formatted', 'pct_formatted', 'display_text'])1143 1144 with row1_col4:1145 # Header1146 st.markdown('<div class="card-title" style="margin-bottom: 0;">By Product</div>', unsafe_allow_html=True)1147 1148 # Prepare data for Sales by Product1149 # We need to calculate percentage of total sales for the label1150 # Note: 'total_sales' variable holds the sum of ALL sales, not just top 10.1151 # But for the bar chart visualization, we might want the percentage relative to the total sales of the period.1152 1153 if not top_products.empty:1154 top_products = top_products.iloc[::-1] # Reverse order for Plotly to show top item at top1155 top_products['pct'] = top_products['sales'] / total_sales1156 top_products['pct_formatted'] = top_products['pct'].apply(lambda x: f"{x:.1%}")1157 top_products['sales_formatted'] = top_products['sales'].apply(format_currency)1158 1159 # Truncate name for display: Take only the part before " - " or "/"1160 top_products['display_name'] = top_products['product_name'].apply(lambda x: x.replace('/', '-').split('-')[0].strip() if x else "Unknown")1161 1162 fig = go.Figure()1163 1164 fig.add_trace(go.Bar(1165 y=top_products['product_code'], # Keep ID for y-axis logic1166 x=[total_sales] * len(top_products),1167 orientation='h',1168 marker_color='#78898F',1169 hoverinfo='skip',1170 showlegend=False,1171 width=0.4,1172 text=top_products['sales_formatted'],1173 textposition='inside',1174 insidetextanchor='end',1175 textfont=dict(color='white', size=18)1176 ))1177 1178 fig.add_trace(go.Bar(1179 y=top_products['product_code'],1180 x=top_products['sales'],1181 orientation='h',1182 marker_color='#984216',1183 text=top_products['pct_formatted'],1184 textposition='outside',1185 textfont=dict(color='white', size=14),1186 hoverinfo='y+x',1187 customdata=top_products[['image_url']], # Pass image URL1188 hovertemplate='%{y}: %{x:$,.0f}<br>Click to view image<extra></extra>',1189 showlegend=False,1190 width=0.41191 ))1192 1193 # Add clickable labels as a Scatter trace1194 # Use product_code for the text label1195 fig.add_trace(go.Scatter(1196 y=top_products['product_code'],1197 x=[-total_sales * 0.02] * len(top_products), # Slightly to the left of 01198 mode='text',1199 text=top_products['product_code'], # Use SKU here1200 textposition='middle left',