import json import mysql.connector from database.connection import get_db_connection import logging from datetime import datetime, timedelta, date # 생산 계획 추가 def add_production_plan(plan_data): try: with get_db_connection() as conn: with conn.cursor(dictionary=True) as cursor: logging.info(f"DB에 추가될 최종 plan_data: {json.dumps(plan_data, indent=4, ensure_ascii=False)}") sql = """ INSERT INTO production_plans ( plan_name, customer_name, customer_id, yarn_name, yarn_inventory_id, bond_name, bond_inventory_id, requested_quantity_kg, start_date, end_date, production_days, status, hourly_net_cost_per_machine, hourly_profit_per_machine, plan_total_material_cost, yarn_diameter, yarn_k, ports, cycle_time, cut_length, work_hours_day, work_days_month, num_machines, plan_net_processing_cost, plan_company_margin, plan_processing_fee, plan_yarn_cost, plan_bond_cost, plan_estimated_sales ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ) """ cursor.execute(sql, ( plan_data.get('plan_name'), plan_data.get('customer_name'), plan_data.get('customer_id'), plan_data.get('yarn_name'), plan_data.get('yarn_inventory_id'), plan_data.get('bond_name'), plan_data.get('bond_inventory_id'), plan_data.get('requested_quantity_kg'), plan_data.get('start_date'), plan_data.get('end_date'), plan_data.get('production_days'), plan_data.get('status', 'Scheduled'), plan_data.get('hourly_net_cost_per_machine'), plan_data.get('hourly_profit_per_machine'), plan_data.get('plan_total_material_cost'), plan_data.get('yarn_diameter'), plan_data.get('yarn_k'), plan_data.get('ports'), plan_data.get('cycle_time'), plan_data.get('cut_length'), plan_data.get('work_hours_day'), plan_data.get('work_days_month'), plan_data.get('num_machines'), plan_data.get('plan_net_processing_cost'), plan_data.get('plan_company_margin'), plan_data.get('plan_processing_fee'), plan_data.get('plan_yarn_cost'), plan_data.get('plan_bond_cost'), plan_data.get('plan_estimated_sales') )) conn.commit() return cursor.lastrowid except mysql.connector.Error as err: logging.error(f"Error adding production plan: {err}") return None # 특정 생산 계획에 설비 할당 정보 추가 def add_equipment_assignments_to_plan(plan_id, assignments): try: with get_db_connection() as conn: with conn.cursor(dictionary=True) as cursor: sql = """ INSERT INTO plan_assignments ( plan_id, equipment_id, start_date, end_date, works_on_saturday, works_on_sunday ) VALUES (%s, %s, %s, %s, %s, %s) """ assignment_data = [ (plan_id, assign['equipment_id'], assign['start_date'], assign['end_date'], 1 if assign.get('works_on_saturday') else 0, 1 if assign.get('works_on_sunday') else 0) for assign in assignments ] cursor.executemany(sql, assignment_data) conn.commit() return True except mysql.connector.Error as err: logging.error(f"Error adding equipment assignments: {err}") return False # 모든 생산 계획 목록 조회 (설비 할당 정보 포함) def get_all_production_plans(): try: with get_db_connection() as conn: with conn.cursor(dictionary=True) as cursor: cursor.execute("SELECT * FROM production_plans ORDER BY start_date") plans = cursor.fetchall() if not plans: return [] sql_assignments = """ SELECT pa.*, e.name as equipment_name FROM plan_assignments pa JOIN equipment e ON pa.equipment_id = e.id """ cursor.execute(sql_assignments) assignments = cursor.fetchall() plan_map = {plan['plan_id']: plan for plan in plans} for plan in plans: plan['assigned_equipment'] = [] for assign in assignments: if assign['plan_id'] in plan_map: assign['works_on_saturday'] = bool(assign['works_on_saturday']) assign['works_on_sunday'] = bool(assign['works_on_sunday']) plan_map[assign['plan_id']]['assigned_equipment'].append(assign) return list(plan_map.values()) except mysql.connector.Error as err: logging.error(f"Error getting all production plans: {err}") return [] # 특정 기간에 사용 가능한 설비 목록 검색 def find_available_equipment(start_date_str, end_date_str, works_on_saturday, works_on_sunday): try: with get_db_connection() as conn: with conn.cursor(dictionary=True) as cursor: cursor.execute("SELECT id, name FROM equipment") all_equipment = cursor.fetchall() return all_equipment except mysql.connector.Error as err: logging.error(f"Error finding all equipment: {err}") return [] # 개별 설비 할당 정보 수정 def update_plan_assignment(plan_id, assignment_data): conn = None try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) conn.start_transaction() sql_update_assignment = """ UPDATE plan_assignments SET start_date = %s, end_date = %s, works_on_saturday = %s, works_on_sunday = %s WHERE assignment_id = %s """ cursor.execute(sql_update_assignment, ( assignment_data.get('start_date'), assignment_data.get('end_date'), 1 if assignment_data.get('works_on_saturday') else 0, 1 if assignment_data.get('works_on_sunday') else 0, assignment_data.get('assignment_id') )) cursor.execute("SELECT MIN(start_date) as min_start, MAX(end_date) as max_end FROM plan_assignments WHERE plan_id = %s", (plan_id,)) plan_dates = cursor.fetchone() if plan_dates and plan_dates['min_start'] and plan_dates['max_end']: cursor.execute("UPDATE production_plans SET start_date = %s, end_date = %s WHERE plan_id = %s", (plan_dates['min_start'], plan_dates['max_end'], plan_id)) conn.commit() return True except mysql.connector.Error as err: if conn: conn.rollback() logging.error(f"Error updating plan assignment for plan_id {plan_id}: {err}") return False finally: if conn and conn.is_connected(): cursor.close() conn.close() # 생산 계획 삭제 def delete_production_plan(plan_id): try: with get_db_connection() as conn: with conn.cursor(dictionary=True) as cursor: cursor.execute("DELETE FROM plan_assignments WHERE plan_id = %s", (plan_id,)) cursor.execute("DELETE FROM production_plans WHERE plan_id = %s", (plan_id,)) conn.commit() return True except mysql.connector.Error as err: logging.error(f"Error deleting production plan: {err}") return False # 가동일 분배 조정 def adjust_and_distribute_assignments(plan_id, source_assignment, distribution): conn = None try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) conn.start_transaction() update_source_sql = """ UPDATE plan_assignments SET start_date=%s, end_date=%s, works_on_saturday=%s, works_on_sunday=%s WHERE assignment_id=%s """ cursor.execute(update_source_sql, ( source_assignment['start_date'], source_assignment['end_date'], 1 if source_assignment['works_on_saturday'] else 0, 1 if source_assignment['works_on_sunday'] else 0, source_assignment['assignment_id'] )) for assign_id_str, days_to_add in distribution.items(): days_to_add = int(days_to_add) if days_to_add <= 0: continue assign_id = int(assign_id_str) cursor.execute("SELECT * FROM plan_assignments WHERE assignment_id = %s", (assign_id,)) target = cursor.fetchone() if not target: raise Exception(f"Target assignment with ID {assign_id} not found.") current_end_date = target['end_date'] works_sat = bool(target['works_on_saturday']) works_sun = bool(target['works_on_sunday']) new_end_date = current_end_date days_added_count = 0 while days_added_count < days_to_add: new_end_date += timedelta(days=1) weekday = new_end_date.weekday() if (weekday < 5) or (weekday == 5 and works_sat) or (weekday == 6 and works_sun): days_added_count += 1 cursor.execute("UPDATE plan_assignments SET end_date = %s WHERE assignment_id = %s", (new_end_date.isoformat(), assign_id)) cursor.execute("SELECT MIN(start_date) as min_start, MAX(end_date) as max_end FROM plan_assignments WHERE plan_id = %s", (plan_id,)) plan_dates = cursor.fetchone() if plan_dates and plan_dates['min_start'] and plan_dates['max_end']: cursor.execute("UPDATE production_plans SET start_date = %s, end_date = %s WHERE plan_id = %s", (plan_dates['min_start'], plan_dates['max_end'], plan_id)) conn.commit() return True except Exception as err: if conn: conn.rollback() logging.error(f"Error during assignment redistribution for plan_id {plan_id}: {err}") return False finally: if conn and conn.is_connected(): cursor.close() conn.close() # 생산 계획 수정 def update_production_plan(plan_id, plan_data, assigned_machine_ids): conn = None try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) conn.start_transaction() cursor.execute("DELETE FROM plan_assignments WHERE plan_id = %s", (plan_id,)) update_sql = """ UPDATE production_plans SET plan_name = %s, customer_name = %s, customer_id = %s, yarn_name = %s, yarn_inventory_id = %s, bond_name = %s, bond_inventory_id = %s, requested_quantity_kg = %s, start_date = %s, end_date = %s, production_days = %s, status = %s, hourly_net_cost_per_machine = %s, hourly_profit_per_machine = %s, plan_total_material_cost = %s, yarn_diameter = %s, yarn_k = %s, ports = %s, cycle_time = %s, cut_length = %s, work_hours_day = %s, work_days_month = %s, num_machines = %s, plan_net_processing_cost = %s, plan_company_margin = %s, plan_processing_fee = %s, plan_yarn_cost = %s, plan_bond_cost = %s, plan_estimated_sales = %s WHERE plan_id = %s """ cursor.execute(update_sql, ( plan_data.get('plan_name'), plan_data.get('customer_name'), plan_data.get('customer_id'), plan_data.get('yarn_name'), plan_data.get('yarn_inventory_id'), plan_data.get('bond_name'), plan_data.get('bond_inventory_id'), plan_data.get('requested_quantity_kg'), plan_data.get('start_date'), plan_data.get('end_date'), plan_data.get('production_days'), plan_data.get('status', 'Scheduled'), plan_data.get('hourly_net_cost_per_machine'), plan_data.get('hourly_profit_per_machine'), plan_data.get('plan_total_material_cost'), plan_data.get('yarn_diameter'), plan_data.get('yarn_k'), plan_data.get('ports'), plan_data.get('cycle_time'), plan_data.get('cut_length'), plan_data.get('work_hours_day'), plan_data.get('work_days_month'), plan_data.get('num_machines'), plan_data.get('plan_net_processing_cost'), plan_data.get('plan_company_margin'), plan_data.get('plan_processing_fee'), plan_data.get('plan_yarn_cost'), plan_data.get('plan_bond_cost'), plan_data.get('plan_estimated_sales'), plan_id )) if assigned_machine_ids: assign_sql = """ INSERT INTO plan_assignments ( plan_id, equipment_id, start_date, end_date, works_on_saturday, works_on_sunday ) VALUES (%s, %s, %s, %s, %s, %s) """ assignment_data = [ ( plan_id, eq_id, plan_data.get('start_date'), plan_data.get('end_date'), 1 if plan_data.get('works_on_saturday') else 0, 1 if plan_data.get('works_on_sunday') else 0 ) for eq_id in assigned_machine_ids ] cursor.executemany(assign_sql, assignment_data) conn.commit() return True except Exception as err: if conn: conn.rollback() logging.error(f"Error updating production plan for plan_id {plan_id}: {err}") return False finally: if conn and conn.is_connected(): cursor.close() conn.close()