voice-gateway-service/src/core/callcenter/dao.py

#!/usr/bin/env python3
# encoding:utf-8

import json
from . import db
from datetime import datetime


class Agent(db.Model):
    __tablename__ = 'sys_user'
    __table_args__ = {
        'comment': '坐席信息表',
        'mysql_engine': 'InnoDB',
        'mysql_charset': 'utf8mb4'
    }

    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True, comment='主键')
    saas_id = db.Column(db.String(16), nullable=False, default='', comment='租户隔离')
    agent_num = db.Column(db.String(32), nullable=False, default='', comment='坐席工号')
    agent_name = db.Column(db.String(32), nullable=False, default='', comment='坐席姓名')
    out_id = db.Column(db.String(32), nullable=False, default='', comment='外部id')
    agent_pwd = db.Column(db.String(64), nullable=False, default='', comment='坐席密码')
    agent_type = db.Column(db.SmallInteger, nullable=False, default=0, comment='坐席类型 0:普通坐席; 1:组长; 2:主管')
    phone_num = db.Column(db.String(32), nullable=False, default='0', comment='分机号')
    distribute = db.Column(db.SmallInteger, nullable=False, default=1, comment='分配标志 0:不参与排队; 1:参与排队')
    agent_state = db.Column(db.SmallInteger, nullable=False, default=0, comment='账号状态 0:可用; 1:禁用')
    identity_type = db.Column(db.SmallInteger, nullable=False, default=0, comment='身份标识')
    is_delete = db.Column(db.SmallInteger, nullable=False, default=0, comment='删除标识')
    update_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow, comment='更新时间')
    create_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow, comment='创建时间')

    __table_args__ = (
        db.UniqueConstraint('saas_id', 'agent_num', name='uniq_vcc_id_agent_num'),
        db.Index('idx_saas_id_out_id', 'saas_id', 'out_id'),
        db.Index('idx_saas_id_phone_num', 'saas_id', 'phone_num')
    )

    def __repr__(self):
        return json.dumps(self.to_dict())

    def to_dict(self):
        return {
            'id': self.id,
            'saas_id': self.saas_id,
            'agent_num': self.agent_num,
            'agent_name': self.agent_name,
            'out_id': self.out_id,
            'agent_pwd': self.agent_pwd,
            'agent_type': self.agent_type,
            'phone_num': self.phone_num,
            'distribute': self.distribute,
            'agent_state': self.agent_state,
            'identity_type': self.identity_type,
            'is_delete': self.is_delete,
            'update_time': self.update_time.isoformat() if self.update_time else None,
            'create_time': self.create_time.isoformat() if self.create_time else None,
        }


class AgentActionLog(db.Model):
    __tablename__ = 'c_agent_action_log'
    __table_args__ = {
        'comment': '坐席行为日志表',
        'mysql_engine': 'InnoDB',
        'mysql_charset': 'utf8mb4'
    }

    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True, comment='主键')
    saas_id = db.Column(db.String(16), nullable=False, default='', comment='租户隔离')
    agent_num = db.Column(db.String(32), nullable=False, default='', comment='坐席工号')
    out_id = db.Column(db.String(32), nullable=False, default='', comment='外部id')
    action_type = db.Column(db.SmallInteger, nullable=False, default=0, comment='行为类型')
    check_state = db.Column(db.SmallInteger, nullable=False, default=-1, comment='签入或签出')
    pre_check_state = db.Column(db.SmallInteger, nullable=False, default=-1, comment='上一次签入或签出')
    service_state = db.Column(db.SmallInteger, nullable=False, default=-1, comment='坐席状态')
    pre_service_state = db.Column(db.SmallInteger, nullable=False, default=-1, comment='上一次坐席状态')
    check_state_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime(2000, 1, 1), comment='签入或签出时间')
    pre_check_state_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime(2000, 1, 1), comment='上一次签入或签出时间')
    service_state_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime(2000, 1, 1), comment='坐席状态变更时间')
    pre_service_state_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime(2000, 1, 1), comment='上一次坐席状态变更时间')
    check_state_duration = db.Column(db.BigInteger, nullable=False, default=0, comment='行为持续时间')
    service_state_duration = db.Column(db.BigInteger, nullable=False, default=0, comment='状态持续时间')
    task_id = db.Column(db.String(32), nullable=False, default='', comment='任务Id')
    service_id = db.Column(db.String(32), nullable=False, default='', comment='人工组id')
    event_type = db.Column(db.Integer, nullable=False, default=0, comment='日志事件类型')
    event_desc = db.Column(db.String(100), nullable=False, default='', comment='日志事件描述')
    is_delete = db.Column(db.SmallInteger, nullable=False, default=0, comment='删除标识')
    update_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow, comment='更新时间')
    create_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow, comment='创建时间')

    __table_args__ = (
        db.Index('idx_create_time', 'create_time'),
        db.Index('idx_saas_id_agent_num', 'saas_id', 'agent_num'),
        db.Index('idx_saas_id_out_id', 'saas_id', 'out_id'),
        db.Index('idx_saas_id_task_id', 'saas_id', 'task_id'),
        db.Index('idx_update_time', 'update_time'),
    )

    def __repr__(self):
        return json.dumps(self.to_dict())

    def to_dict(self):
        return {
            'id': self.id,
            'saas_id': self.saas_id,
            'agent_num': self.agent_num,
            'out_id': self.out_id,
            'action_type': self.action_type,
            'check_state': self.check_state,
            'pre_check_state': self.pre_check_state,
            'service_state': self.service_state,
            'pre_service_state': self.pre_service_state,
            'check_state_time': self.check_state_time.isoformat() if self.check_state_time else None,
            'pre_check_state_time': self.pre_check_state_time.isoformat() if self.pre_check_state_time else None,
            'service_state_time': self.service_state_time.isoformat() if self.service_state_time else None,
            'pre_service_state_time': self.pre_service_state_time.isoformat() if self.pre_service_state_time else None,
            'check_state_duration': self.check_state_duration,
            'service_state_duration': self.service_state_duration,
            'task_id': self.task_id,
            'service_id': self.service_id,
            'event_type': self.event_type,
            'event_desc': self.event_desc,
            'is_delete': self.is_delete,
            'update_time': self.update_time.isoformat() if self.update_time else None,
            'create_time': self.create_time.isoformat() if self.create_time else None,
        }


class AgentLog(db.Model):
    __tablename__ = 'c_agent_log'
    __table_args__ = {
        'comment': '坐席日志表',
        'mysql_engine': 'InnoDB',
        'mysql_charset': 'utf8mb4'
    }

    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True, comment='主键')
    saas_id = db.Column(db.String(16), nullable=False, default='', comment='租户隔离')
    flow_id = db.Column(db.String(64), nullable=False, default='', comment='人工外呼流程ID')
    agent_num = db.Column(db.String(32), nullable=False, default='', comment='坐席工号')
    out_id = db.Column(db.String(32), nullable=False, default='', comment='外部id')
    event_type = db.Column(db.SmallInteger, nullable=False, default=0, comment='事件类型')
    event = db.Column(db.String(32), nullable=False, default='0', comment='事件')
    content = db.Column(db.String(64), nullable=False, default='', comment='描述')
    is_delete = db.Column(db.SmallInteger, nullable=False, default=0, comment='删除标识')
    update_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow, comment='更新时间')
    create_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow, comment='创建时间')

    __table_args__ = (
        db.Index('idx_saas_id_agent_num', 'saas_id', 'agent_num'),
        db.Index('idx_saas_id_out_id', 'saas_id', 'out_id')
    )

    def __repr__(self):
        return json.dumps(self.to_dict())

    def to_dict(self):
        return {
            'id': self.id,
            'saas_id': self.saas_id,
            'flow_id': self.flow_id,
            'agent_num': self.agent_num,
            'out_id': self.out_id,
            'event_type': self.event_type,
            'event': self.event,
            'content': self.content,
            'is_delete': self.is_delete,
            'update_time': self.update_time.isoformat() if self.update_time else None,
            'create_time': self.create_time.isoformat() if self.create_time else None,
        }


class AgentMonitor(db.Model):
    __tablename__ = 'c_agent_monitor'
    __table_args__ = {
        'comment': '坐席监控表',
        'mysql_engine': 'InnoDB',
        'mysql_charset': 'utf8mb4'
    }

    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True, comment='主键')
    saas_id = db.Column(db.String(16), nullable=False, default='', comment='租户隔离')
    agent_num = db.Column(db.String(32), nullable=False, default='', comment='坐席工号')
    out_id = db.Column(db.String(32), nullable=False, default='', comment='外部id')
    identity_type = db.Column(db.SmallInteger, nullable=False, default=0, comment='身份标识')
    check_state = db.Column(db.SmallInteger, nullable=False, default=1, comment='是否签入 0:是 1:否 默认未签入')
    check_scene = db.Column(db.String(16), nullable=False, default='', comment='迁入场景')
    check_in_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow, comment='签入时间')
    check_out_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow, comment='签出时间')
    service_state = db.Column(db.SmallInteger, nullable=False, default=0, comment='坐席服务状态 0: 未登录(签出) 1: 置忙 2: 置闲 3: 通话中 4: 后处理 5: 拨号中')
    busy_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow, comment='置忙时间')
    idle_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow, comment='置闲时间')
    call_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow, comment='接通时间')
    hang_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow, comment='挂断时间')
    heart_state = db.Column(db.SmallInteger, nullable=False, default=0, comment='心跳状态 0: 默认 1：正常  2: 异常')
    heart_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow, comment='正常心跳时间')
    session_id = db.Column(db.String(64), nullable=False, default='', comment='sessionId')
    is_delete = db.Column(db.SmallInteger, nullable=False, default=0, comment='删除标识')
    update_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow, comment='更新时间')
    create_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow, comment='创建时间')

    __table_args__ = (
        db.UniqueConstraint('saas_id', 'agent_num', name='uniq_saas_id_agent_num'),
        db.Index('idx_saas_id_out_id', 'saas_id', 'out_id')
    )

    def __repr__(self):
        return json.dumps(self.to_dict())

    def to_dict(self):
        return {
            'id': self.id,
            'saas_id': self.saas_id,
            'agent_num': self.agent_num,
            'out_id': self.out_id,
            'identity_type': self.identity_type,
            'check_state': self.check_state,
            'check_scene': self.check_scene,
            'check_in_time': self.check_in_time.isoformat() if self.check_in_time else None,
            'check_out_time': self.check_out_time.isoformat() if self.check_out_time else None,
            'service_state': self.service_state,
            'busy_time': self.busy_time.isoformat() if self.busy_time else None,
            'idle_time': self.idle_time.isoformat() if self.idle_time else None,
            'call_time': self.call_time.isoformat() if self.call_time else None,
            'hang_time': self.hang_time.isoformat() if self.hang_time else None,
            'heart_state': self.heart_state,
            'heart_time': self.heart_time.isoformat() if self.heart_time else None,
            'session_id': self.session_id,
            'is_delete': self.is_delete,
            'update_time': self.update_time.isoformat() if self.update_time else None,
            'create_time': self.create_time.isoformat() if self.create_time else None,
        }


class Phone(db.Model):
    __tablename__ = 'c_phone'
    __table_args__ = {
        'comment': '分机信息表',
        'mysql_engine': 'InnoDB',
        'mysql_charset': 'utf8mb4'
    }

    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True, comment='主键')
    saas_id = db.Column(db.String(16), nullable=False, default='', comment='租户隔离')
    phone_num = db.Column(db.String(32), nullable=False, default='0', comment='分机号')
    phone_pwd = db.Column(db.String(32), nullable=False, default='', comment='分机密码')
    sip_server = db.Column(db.String(64), nullable=False, default='', comment='SIP服务器')
    wss_server = db.Column(db.String(64), nullable=False, default='', comment='WSS服务器')
    ice_server = db.Column(db.String(64), nullable=False, default='', comment='ICE服务器')
    is_delete = db.Column(db.SmallInteger, nullable=False, default=0, comment='删除标识')
    update_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow, comment='更新时间')
    create_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow, comment='创建时间')

    __table_args__ = (
        db.Index('idx_vcc_id_phone_num', 'saas_id', 'phone_num'),
    )

    def __repr__(self):
        return json.dumps(self.to_dict())

    def to_dict(self):
        return {
            'id': self.id,
            'saas_id': self.saas_id,
            'phone_num': self.phone_num,
            'phone_pwd': self.phone_pwd,
            'sip_server': self.sip_server,
            'wss_server': self.wss_server,
            'ice_server': self.ice_server,
            'is_delete': self.is_delete,
            'update_time': self.update_time.isoformat() if self.update_time else None,
            'create_time': self.create_time.isoformat() if self.create_time else None,
        }


class HumanService(db.Model):
    __tablename__ = 'c_human_service'
    __table_args__ = {'comment': '人工服务组信息'}

    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True, comment='主键')
    saas_id = db.Column(db.String(16), nullable=False, default='', comment='租户隔离')
    service_id = db.Column(db.String(32), nullable=False, default='', comment='人工组编号')
    service_name = db.Column(db.String(32), nullable=False, default='', comment='人工组名称')
    schedule_key = db.Column(db.String(32), nullable=False, default='', comment='日程编号')
    effective = db.Column(db.String(32), nullable=False, default='0', comment='状态 0：启用（可以或正在进行外呼）1：暂停')
    service_start = db.Column(db.String(32), nullable=False, default='', comment='任务开始时间 yyyyMMddHHmmss')
    service_end = db.Column(db.String(32), nullable=False, default='22200101000000', comment='任务结束时间 yyyyMMddHHmmss')
    distribute = db.Column(db.String(32), nullable=False, default='3', comment='分配策略')
    call_in_limit = db.Column(db.String(32), nullable=False, default='0', comment='呼入线数 限制 固定值0')
    record_type = db.Column(db.String(32), nullable=False, default='1', comment='录音方式 默认0 0：服务器不录 1：服务器录 4：按间隔抽样录 5：按百分比抽样录 固定值 1')
    queue_length = db.Column(db.String(32), nullable=False, default='100', comment='排队长度 固定值 100')
    queue_warning = db.Column(db.String(32), nullable=False, default='100', comment='排队告警 长度 固定值 100')
    queue_timeout = db.Column(db.String(32), nullable=False, default='60', comment='排队超时 时间 固定值 60')
    sum_queue_time = db.Column(db.String(32), nullable=False, default='90', comment='所有排队 时间  单位：秒;默认300： 固定值 90')
    connect_type = db.Column(db.String(32), nullable=False, default='0', comment='连接坐席 类型 默认0 0盲转连接 1桥接连接并超时重排 固定值 0')
    queue_music = db.Column(db.String(32), nullable=False, default='', comment='排队音 全局配置')
    direct_queue_type = db.Column(db.String(32), nullable=False, default='', comment='直接连接 媒体类型 默认1 接入码直接转人工服务能听到排队音 0：直接排队不连接媒体； 1：直接排队连接媒体 全局配置')
    is_delete = db.Column(db.Boolean, nullable=False, default=False, comment='删除标识')
    update_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow, comment='更新时间')
    create_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow, comment='创建时间')

    # Indexes
    __table_args__ = (
        db.Index('idx_create_time', 'create_time'),
        db.Index('idx_update_time', 'update_time'),
        db.Index('idx_saas_id_service_id', 'saas_id', 'service_id'),
    )

    def to_dict(self):
        return {
            'id': self.id,
            'saas_id': self.saas_id,
            'service_id': self.service_id,
            'service_name': self.service_name,
            'schedule_key': self.schedule_key,
            'effective': self.effective,
            'service_start': self.service_start,
            'service_end': self.service_end,
            'distribute': self.distribute,
            'call_in_limit': self.call_in_limit,
            'record_type': self.record_type,
            'queue_length': self.queue_length,
            'queue_warning': self.queue_warning,
            'queue_timeout': self.queue_timeout,
            'sum_queue_time': self.sum_queue_time,
            'connect_type': self.connect_type,
            'queue_music': self.queue_music,
            'direct_queue_type': self.direct_queue_type,
            'is_delete': self.is_delete,
            'update_time': self.update_time.isoformat() if self.update_time else None,
            'create_time': self.create_time.isoformat() if self.create_time else None,
        }


class HumanServiceMap(db.Model):
    __tablename__ = 'c_human_service_map'
    __table_args__ = {'comment': '人工服务组与坐席关系'}

    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True, comment='主键')
    saas_id = db.Column(db.String(16), nullable=False, default='', comment='租户隔离')
    service_id = db.Column(db.String(32), nullable=False, default='', comment='人工组编号')
    service_type = db.Column(db.String(32), nullable=False, default='0', comment='服务类型')
    agent_id = db.Column(db.String(32), nullable=False, default='', comment='坐席工号')
    state = db.Column(db.Boolean, nullable=False, default=0, comment='状态 1激活(签入)')
    is_delete = db.Column(db.Boolean, nullable=False, default=0, comment='删除标识')
    update_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow, comment='更新时间')
    create_time = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow, comment='创建时间')

    # Indexes
    __table_args__ = (
        db.Index('idx_create_time', 'create_time'),
        db.Index('idx_update_time', 'update_time'),
        db.Index('idx_saas_id_service_id_agent_id', 'saas_id', 'service_id', 'agent_id'),
    )

    def to_dict(self):
        return {
            'id': self.id,
            'saas_id': self.vcc_id,
            'service_id': self.service_id,
            'service_type': self.service_type,
            'agent_id': self.agent_id,
            'state': self.state,
            'is_delete': self.is_delete,
            'update_time': self.update_time.isoformat() if self.update_time else None,
            'create_time': self.create_time.isoformat() if self.create_time else None,
        }



class Whitelist(db.Model):
    __tablename__ = 't_whitelist'
    __table_args__ = {'comment': '白名单配置表'}

    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='主键')
    phone = db.Column(db.String(20), nullable=False, comment='电话号码')
    description = db.Column(db.String(255), nullable=True, comment='描述说明（备注）')
    del_flag = db.Column(db.Boolean, nullable=False, default=False, comment='删除标志（0代表存在 2代表删除）')
    revision = db.Column(db.Integer, nullable=True, comment='乐观锁')
    create_by = db.Column(db.String(32), nullable=True, comment='创建人')
    create_time = db.Column(db.DateTime, nullable=True, default=datetime.utcnow, comment='创建时间')
    update_by = db.Column(db.String(32), nullable=True, comment='更新人')
    update_time = db.Column(db.DateTime, nullable=True, onupdate=datetime.utcnow, comment='更新时间')
    remark = db.Column(db.String(500), nullable=True, comment='备注')

    def to_dict(self):
        return {
            'id': self.id,
            'phone': self.phone,
            'description': self.description,
            'del_flag': self.del_flag,
            'revision': self.revision,
            'create_by': self.create_by,
            'create_time': self.create_time.isoformat() if self.create_time else None,
            'update_by': self.update_by,
            'update_time': self.update_time.isoformat() if self.update_time else None,
            'remark': self.remark,
        }


class CallRecord(db.Model):
    __tablename__ = 't_call_record'
    __table_args__ = {'comment': '通话记录表'}

    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='主键')
    session_id = db.Column(db.String(30), nullable=True, comment='sessionId')
    type = db.Column(db.SmallInteger, nullable=True, comment='呼入分类（0白名单 1AI服务 2传统服务）')
    user_id = db.Column(db.BigInteger, nullable=True, comment='客服ID')
    user_name = db.Column(db.String(255), nullable=True, comment='客服名字')
    service_category = db.Column(db.SmallInteger, nullable=False, comment='服务类型（0人工坐席 1机器人坐席 2机器人转人工）')
    time_begin = db.Column(db.DateTime, nullable=True, comment='通话发起时间')
    time_end = db.Column(db.DateTime, nullable=True, comment='通话结束时间')
    times = db.Column(db.String(30), nullable=True, comment='通话时长（暂时按字符串接收）')
    category = db.Column(db.SmallInteger, nullable=True, comment='通话类型（0呼入 1呼出）')
    status = db.Column(db.SmallInteger, nullable=True, comment='通话状态(0未接听 1已接通)')
    phone = db.Column(db.String(20), nullable=True, comment='电话号码')
    bussiness_type = db.Column(db.String(50), nullable=True, comment='业务类型（创个返回字符串）')
    url = db.Column(db.String(255), nullable=True, comment='录音的地址')
    remark = db.Column(db.String(500), nullable=True, comment='备注')
    has_parsed = db.Column(db.Boolean, nullable=False, default=False, comment='是否已转录音（0否 1是）')
    parsed_voice_content = db.Column(db.Text, nullable=True, comment='通话录音内容')
    del_flag = db.Column(db.Boolean, nullable=False, default=False, comment='删除标志（0代表存在 2代表删除）')
    revision = db.Column(db.Integer, nullable=True, comment='乐观锁')
    create_by = db.Column(db.String(32), nullable=True, default="admin", comment='创建人')
    create_time = db.Column(db.DateTime, nullable=True, default=datetime.utcnow, comment='创建时间')
    update_by = db.Column(db.String(32), nullable=True, default="admin", comment='更新人')
    update_time = db.Column(db.DateTime, nullable=True, onupdate=datetime.utcnow, comment='更新时间')

    def to_dict(self):
        return {
            'id': self.id,
            'session_id': self.session_id,
            'type': self.type,
            'user_id': self.user_id,
            'user_name': self.user_name,
            'service_category': self.service_category,
            'time_begin': self.time_begin.isoformat() if self.time_begin else None,
            'time_end': self.time_end.isoformat() if self.time_end else None,
            'times': self.times,
            'category': self.category,
            'status': self.status,
            'phone': self.phone,
            'bussiness_type': self.bussiness_type,
            'url': self.url,
            'remark': self.remark,
            'has_parsed': self.has_parsed,
            'parsed_voice_content': self.parsed_voice_content,
            'del_flag': self.del_flag,
            'revision': self.revision,
            'create_by': self.create_by,
            'create_time': self.create_time.isoformat() if self.create_time else None,
            'update_by': self.update_by,
            'update_time': self.update_time.isoformat() if self.update_time else None,
        }