import openpyxl from openpyxl import load_workbook from typing import Dict, Any import os from datetime import datetime class StudentExcelService: def __init__(self): self.excel_path = "files/Simulation Test file - clean.xlsx" self.input_sheets = [ 'Assu Sum Mod A', 'Plan Working A', 'Assu Sum Mod B', 'Plan Working B', 'Assu Sum Mod Online', 'Plan Working Online', 'Rollout Plan', 'Concept Org Structure', 'Warehouse (Central)', 'Warehouse (Direct Store)' ] # Field to Excel cell mapping - All 48 fields self.field_mapping = { # Assu Sum Mod A ' Store Size ': {'sheet': 'Assu Sum Mod A', 'cell': 'I6'}, ' No.of Bills p.m - Yr.1 ': {'sheet': 'Assu Sum Mod A', 'cell': 'I7'}, ' No of items per bill ': {'sheet': 'Assu Sum Mod A', 'cell': 'I8'}, ' Avg Price per item ': {'sheet': 'Assu Sum Mod A', 'cell': 'I9'}, ' COGS ': {'sheet': 'Assu Sum Mod A', 'cell': 'I10'}, ' Income ': {'sheet': 'Assu Sum Mod A', 'cell': 'I10'}, ' Rent p.m ': {'sheet': 'Assu Sum Mod A', 'cell': 'I10'}, ' Employee Cost ': {'sheet': 'Assu Sum Mod A', 'cell': 'I10'}, ' COGS ': {'sheet': 'Assu Sum Mod A', 'cell': 'I10'}, ' COGS ': {'sheet': 'Assu Sum Mod A', 'cell': 'I10'}, ' COGS ': {'sheet': 'Assu Sum Mod A', 'cell': 'I10'}, ' COGS ': {'sheet': 'Assu Sum Mod A', 'cell': 'I10'}, ' COGS ': {'sheet': 'Assu Sum Mod A', 'cell': 'I10'}, ' COGS ': {'sheet': 'Assu Sum Mod A', 'cell': 'I10'}, ' COGS ': {'sheet': 'Assu Sum Mod A', 'cell': 'I10'}, ' COGS ': {'sheet': 'Assu Sum Mod A', 'cell': 'I10'}, # Assu Sum Mod B 'carpet_area_b': {'sheet': 'Assu Sum Mod B', 'cell': 'I6'}, 'capex_b': {'sheet': 'Assu Sum Mod B', 'cell': 'I7'}, 'it_cost_b': {'sheet': 'Assu Sum Mod B', 'cell': 'I8'}, 'sales_psf_b': {'sheet': 'Assu Sum Mod B', 'cell': 'I9'}, 'gp_percent_b': {'sheet': 'Assu Sum Mod B', 'cell': 'I10'}, # Assu Sum Mod Online 'online_traffic': {'sheet': 'Assu Sum Mod Online', 'cell': 'E6'}, 'conversion_rate': {'sheet': 'Assu Sum Mod Online', 'cell': 'E7'}, 'avg_order_value': {'sheet': 'Assu Sum Mod Online', 'cell': 'E8'}, 'fulfillment_cost': {'sheet': 'Assu Sum Mod Online', 'cell': 'E9'}, # Rollout Plan 'stores_y1_bangalore': {'sheet': 'Rollout Plan', 'cell': 'D6'}, 'stores_y2_bangalore': {'sheet': 'Rollout Plan', 'cell': 'E6'}, 'stores_y1_chennai': {'sheet': 'Rollout Plan', 'cell': 'D7'}, 'stores_y2_chennai': {'sheet': 'Rollout Plan', 'cell': 'E7'}, 'stores_y1_hyderabad': {'sheet': 'Rollout Plan', 'cell': 'D8'}, 'stores_y2_hyderabad': {'sheet': 'Rollout Plan', 'cell': 'E8'}, # Concept Org Structure 'ceo_salary': {'sheet': 'Concept Org Structure', 'cell': 'D6'}, 'cfo_salary': {'sheet': 'Concept Org Structure', 'cell': 'D7'}, 'marketing_head_salary': {'sheet': 'Concept Org Structure', 'cell': 'D8'}, 'operations_head_salary': {'sheet': 'Concept Org Structure', 'cell': 'D9'}, 'hr_head_salary': {'sheet': 'Concept Org Structure', 'cell': 'D10'}, # Warehouse Central 'warehouse_manager_salary': {'sheet': 'Warehouse (Central)', 'cell': 'D7'}, 'warehouse_staff_count': {'sheet': 'Warehouse (Central)', 'cell': 'C8'}, 'warehouse_staff_salary': {'sheet': 'Warehouse (Central)', 'cell': 'D8'}, 'warehouse_rent': {'sheet': 'Warehouse (Central)', 'cell': 'D9'}, # Warehouse Direct Store 'delivery_manager_salary': {'sheet': 'Warehouse (Direct Store)', 'cell': 'D6'}, 'delivery_staff_count': {'sheet': 'Warehouse (Direct Store)', 'cell': 'C7'}, 'delivery_staff_salary': {'sheet': 'Warehouse (Direct Store)', 'cell': 'D7'}, 'delivery_vehicle_cost': {'sheet': 'Warehouse (Direct Store)', 'cell': 'D8'}, # Plan Working A 'rent_psf_a': {'sheet': 'Plan Working A', 'cell': 'E15'}, 'staff_count_a': {'sheet': 'Plan Working A', 'cell': 'E25'}, 'avg_staff_salary_a': {'sheet': 'Plan Working A', 'cell': 'E26'}, 'electricity_cost_a': {'sheet': 'Plan Working A', 'cell': 'E35'}, 'maintenance_cost_a': {'sheet': 'Plan Working A', 'cell': 'E40'}, # Plan Working B 'rent_psf_b': {'sheet': 'Plan Working B', 'cell': 'E15'}, 'staff_count_b': {'sheet': 'Plan Working B', 'cell': 'E25'}, 'avg_staff_salary_b': {'sheet': 'Plan Working B', 'cell': 'E26'}, 'electricity_cost_b': {'sheet': 'Plan Working B', 'cell': 'E35'}, 'maintenance_cost_b': {'sheet': 'Plan Working B', 'cell': 'E40'}, # Plan Working Online 'tech_team_cost': {'sheet': 'Plan Working Online', 'cell': 'E15'}, 'marketing_spend': {'sheet': 'Plan Working Online', 'cell': 'E20'}, 'platform_commission': {'sheet': 'Plan Working Online', 'cell': 'E25'}, 'payment_gateway': {'sheet': 'Plan Working Online', 'cell': 'E26'}, 'logistics_cost': {'sheet': 'Plan Working Online', 'cell': 'E30'}, } def save_student_data(self, student_id: str, form_data: Dict[str, Any]) -> Dict[str, Any]: """Save student form data to database""" # Store data in a simple file-based database for now import json import os db_dir = "student_data" if not os.path.exists(db_dir): os.makedirs(db_dir) db_file = f"{db_dir}/{student_id}.json" # Save form data to file with open(db_file, 'w') as f: json.dump(form_data, f, indent=2) return {"saved": len(form_data), "student_id": student_id} def update_excel_with_database_data(self, student_id: str) -> Dict[str, Any]: """Update existing Excel file with student data""" # Simulate getting data from database student_data = self._get_student_data_from_db(student_id) # Load existing Excel file wb = load_workbook(self.excel_path) updated_cells = 0 # Update Excel cells with student data for field_name, value in student_data.items(): if field_name in self.field_mapping: mapping = self.field_mapping[field_name] sheet_name = mapping['sheet'] cell_address = mapping['cell'] if sheet_name in wb.sheetnames: ws = wb[sheet_name] ws[cell_address] = value updated_cells += 1 # Force recalculation if hasattr(wb, 'calculation'): wb.calculation.calcMode = 'auto' # Save to timestamped file (avoid permission issues) timestamp = datetime.now().strftime("%Y%m%d_%H%M%S") output_path = self.excel_path.replace('.xlsx', f'_updated_{timestamp}.xlsx') wb.save(output_path) # Update excel_path to point to new file self.excel_path = output_path wb.close() return {"updated_cells": updated_cells, "excel_path": output_path} def _get_student_data_from_db(self, student_id: str) -> Dict[str, Any]: """Get student data from database""" import json import os db_file = f"student_data/{student_id}.json" if not os.path.exists(db_file): return {} # No data found # Load data from file with open(db_file, 'r') as f: return json.load(f)