Control_Panel/server/customer/db_manager.py

import mysql.connector
from database.connection import get_db_connection
import logging

# 고객사 추가
def add_customer_to_db(customer_data):
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                sql = """
                    INSERT INTO customers (name_kr, name_en, business_registration_number, contact_number, address)
                    VALUES (%s, %s, %s, %s, %s)
                """
                cursor.execute(sql, (
                    customer_data.get('name_kr'),
                    customer_data.get('name_en'),
                    customer_data.get('business_registration_number'),
                    customer_data.get('contact_number'),
                    customer_data.get('address')
                ))
                conn.commit()
                return cursor.lastrowid
    except mysql.connector.Error as err:
        logging.error(f"Error adding customer: {err}")
        return None

# 고객사 수정
def update_customer_in_db(customer_data):
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                sql = """
                    UPDATE customers SET
                        name_kr = %s,
                        name_en = %s,
                        business_registration_number = %s,
                        contact_number = %s,
                        address = %s
                    WHERE id = %s
                """
                cursor.execute(sql, (
                    customer_data.get('name_kr'),
                    customer_data.get('name_en'),
                    customer_data.get('business_registration_number'),
                    customer_data.get('contact_number'),
                    customer_data.get('address'),
                    customer_data.get('id')
                ))
                conn.commit()
                return True
    except mysql.connector.Error as err:
        logging.error(f"Error updating customer: {err}")
        return False

# 고객사 삭제
def delete_customer_from_db(customer_id):
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                cursor.execute("DELETE FROM sales_activities WHERE customer_id = %s", (customer_id,))
                cursor.execute("DELETE FROM contacts WHERE customer_id = %s", (customer_id,))
                cursor.execute("DELETE FROM customers WHERE id = %s", (customer_id,))
                conn.commit()
                return True
    except mysql.connector.Error as err:
        logging.error(f"Error deleting customer: {err}")
        return False

# 모든 고객사 목록 조회
def get_all_customers_from_db():
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                cursor.execute("SELECT id, name_kr, name_en, business_registration_number, contact_number, address FROM customers")
                return cursor.fetchall()
    except mysql.connector.Error as err:
        logging.error(f"Error getting all customers: {err}")
        return []

# 특정 고객사 상세 정보 조회 (담당자, 영업활동 포함)
def get_customer_details_from_db(customer_id):
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                cursor.execute("SELECT * FROM customers WHERE id = %s", (customer_id,))
                customer_data = cursor.fetchone()
                if not customer_data:
                    return None

                cursor.execute("SELECT * FROM contacts WHERE customer_id = %s", (customer_id,))
                contacts_data = cursor.fetchall()

                cursor.execute("SELECT * FROM sales_activities WHERE customer_id = %s", (customer_id,))
                sales_activities_data = cursor.fetchall()
        
        return {
            "customer": customer_data,
            "contacts": contacts_data,
            "salesActivities": sales_activities_data
        }
    except mysql.connector.Error as err:
        logging.error(f"Error getting customer details: {err}")
        return None

# 담당자 추가
def add_contact_to_db(contact_data):
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                sql = """
                    INSERT INTO contacts (customer_id, name, email, phone_number, position, work_location)
                    VALUES (%s, %s, %s, %s, %s, %s)
                """
                cursor.execute(sql, (
                    contact_data.get('customer_id'),
                    contact_data.get('name'),
                    contact_data.get('email'),
                    contact_data.get('phone_number'),
                    contact_data.get('position'),
                    contact_data.get('work_location')
                ))
                conn.commit()
                return cursor.lastrowid
    except mysql.connector.Error as err:
        logging.error(f"Error adding contact: {err}")
        return None

# 담당자 수정
def update_contact_in_db(contact_data):
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                sql = """
                    UPDATE contacts SET
                        name = %s, email = %s, phone_number = %s, position = %s, work_location = %s
                    WHERE id = %s
                """
                cursor.execute(sql, (
                    contact_data.get('name'),
                    contact_data.get('email'),
                    contact_data.get('phone_number'),
                    contact_data.get('position'),
                    contact_data.get('work_location'),
                    contact_data.get('id')
                ))
                conn.commit()
                return True
    except mysql.connector.Error as err:
        logging.error(f"Error updating contact: {err}")
        return False

# 담당자 삭제
def delete_contact_from_db(contact_id):
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                cursor.execute("DELETE FROM contacts WHERE id = %s", (contact_id,))
                conn.commit()
                return True
    except mysql.connector.Error as err:
        logging.error(f"Error deleting contact: {err}")
        return False

# 담당자 ID로 고객사 ID 조회
def get_customer_id_from_contact_id(contact_id):
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                cursor.execute("SELECT customer_id FROM contacts WHERE id = %s", (contact_id,))
                result = cursor.fetchone()
                return result['customer_id'] if result else None
    except mysql.connector.Error as err:
        logging.error(f"Error getting customer ID from contact: {err}")
        return None

# 영업 활동 추가
def add_sales_activity_to_db(activity_data):
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                sql = """
                    INSERT INTO sales_activities (customer_id, contact_id, activity_date, activity_type, memo)
                    VALUES (%s, %s, %s, %s, %s)
                """
                cursor.execute(sql, (
                    activity_data.get('customer_id'),
                    activity_data.get('contact_id'),
                    activity_data.get('activity_date'),
                    activity_data.get('activity_type'),
                    activity_data.get('memo')
                ))
                conn.commit()
                return cursor.lastrowid
    except mysql.connector.Error as err:
        logging.error(f"Error adding sales activity: {err}")
        return None

# 영업 활동 수정
def update_sales_activity_in_db(activity_data):
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                sql = """
                    UPDATE sales_activities SET
                        contact_id = %s, activity_date = %s, activity_type = %s, memo = %s
                    WHERE id = %s
                """
                cursor.execute(sql, (
                    activity_data.get('contact_id'),
                    activity_data.get('activity_date'),
                    activity_data.get('activity_type'),
                    activity_data.get('memo'),
                    activity_data.get('id')
                ))
                conn.commit()
                return True
    except mysql.connector.Error as err:
        logging.error(f"Error updating sales activity: {err}")
        return False

# 영업 활동 삭제
def delete_sales_activity_from_db(activity_id):
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                cursor.execute("DELETE FROM sales_activities WHERE id = %s", (activity_id,))
                conn.commit()
                return True
    except mysql.connector.Error as err:
        logging.error(f"Error deleting sales activity: {err}")
        return False

# 영업 활동 ID로 고객사 ID 조회
def get_customer_id_from_sales_activity_id(activity_id):
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                cursor.execute("SELECT customer_id FROM sales_activities WHERE id = %s", (activity_id,))
                result = cursor.fetchone()
                return result['customer_id'] if result else None
    except mysql.connector.Error as err:
        logging.error(f"Error getting customer ID from sales activity: {err}")
        return None