Spaces:
Sleeping
Sleeping
File size: 5,011 Bytes
7d72bcf |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 |
from sqlalchemy import create_engine, text
from datetime import datetime
import logging
logger = logging.getLogger(__name__)
class CustomerDBManager:
def __init__(self, customer):
self.customer = customer
self.engine = None
def get_connection(self):
"""Get database connection for the customer"""
if not self.engine:
try:
# Create database URL from customer's database credentials
db_url = f"mysql+pymysql://{self.customer.db_user}:{self.customer.db_password}@{self.customer.db_host}:{self.customer.db_port}/{self.customer.db_name}"
self.engine = create_engine(db_url)
except Exception as e:
logger.error(f"Failed to create database connection for customer {self.customer.id}: {str(e)}")
raise
return self.engine
def save_call_record(self, call_data):
"""Save call record to customer's database"""
try:
engine = self.get_connection()
# Prepare the SQL query
query = text("""
INSERT INTO call_records (
id, customer_id, caller_number, called_number,
transcription, summary, sentiment, keywords,
created_at, updated_at
) VALUES (
:id, :customer_id, :caller_number, :called_number,
:transcription, :summary, :sentiment, :keywords,
:created_at, :updated_at
)
""")
# Prepare the data
now = datetime.utcnow()
record_data = {
'id': call_data.get('id'),
'customer_id': self.customer.id,
'caller_number': call_data.get('caller_number'),
'called_number': call_data.get('called_number'),
'transcription': call_data.get('transcription'),
'summary': call_data.get('summary'),
'sentiment': call_data.get('sentiment'),
'keywords': call_data.get('keywords'),
'created_at': now,
'updated_at': now
}
# Execute the query
with engine.connect() as connection:
connection.execute(query, record_data)
connection.commit()
logger.info(f"Successfully saved call record {call_data.get('id')} for customer {self.customer.id}")
return True
except Exception as e:
logger.error(f"Failed to save call record for customer {self.customer.id}: {str(e)}")
raise
def get_call_record(self, call_id):
"""Retrieve a call record from customer's database"""
try:
engine = self.get_connection()
query = text("""
SELECT * FROM call_records
WHERE id = :call_id AND customer_id = :customer_id
""")
with engine.connect() as connection:
result = connection.execute(query, {
'call_id': call_id,
'customer_id': self.customer.id
}).fetchone()
return dict(result) if result else None
except Exception as e:
logger.error(f"Failed to retrieve call record {call_id} for customer {self.customer.id}: {str(e)}")
raise
def search_call_records(self, filters=None):
"""Search call records in customer's database"""
try:
engine = self.get_connection()
# Build the query based on filters
query = text("""
SELECT * FROM call_records
WHERE customer_id = :customer_id
""")
params = {'customer_id': self.customer.id}
if filters:
if filters.get('start_date'):
query = query.text + " AND created_at >= :start_date"
params['start_date'] = filters['start_date']
if filters.get('end_date'):
query = query.text + " AND created_at <= :end_date"
params['end_date'] = filters['end_date']
if filters.get('caller_number'):
query = query.text + " AND caller_number = :caller_number"
params['caller_number'] = filters['caller_number']
if filters.get('called_number'):
query = query.text + " AND called_number = :called_number"
params['called_number'] = filters['called_number']
query = query.text + " ORDER BY created_at DESC"
with engine.connect() as connection:
results = connection.execute(query, params).fetchall()
return [dict(row) for row in results]
except Exception as e:
logger.error(f"Failed to search call records for customer {self.customer.id}: {str(e)}")
raise |