def list(self, line_id):
user_line_query = (
Session.query(UserLine.line_id, UserLine.extension_id).filter(
UserLine.line_id == line_id).filter(
UserLine.extension_id != None) # noqa
.distinct())
incall_query = (
Session.query(UserLine.line_id,
Extension.id.label('extension_id')).join(
Dialaction,
and_(
Dialaction.action == 'user',
cast(Dialaction.actionarg1,
Integer) == UserLine.user_id,
UserLine.main_line == True)) # noqa
.join(
Incall,
and_(Dialaction.category == 'incall',
cast(Dialaction.categoryval, Integer) == Incall.id)).join(
Extension,
and_(Incall.exten == Extension.exten,
Incall.context == Extension.context)).filter(
UserLine.line_id == line_id))
return [
LineExtension(row.line_id, row.extension_id)
for row in user_line_query.union(incall_query)
]
def check_line_has_users(line_id):
query = (
Session.query(UserLine).filter(UserLine.line_id == line_id).filter(
UserLine.user_id != None) # noqa
.exists())
return Session.query(query).scalar()
def has_secondary_users(user_id, line_id):
exists_query = (
Session.query(UserLine).filter(UserLine.line_id == line_id).filter(
UserLine.user_id != user_id).filter(
UserLine.main_user == False) # noqa
.exists())
return Session.query(exists_query).scalar()
def template_has_sccp_device(template_id):
exists_query = (Session.query(UserFeatures).join(
UserFeatures.main_line_rel).join(UserLine.main_line_rel).filter(
or_(
UserFeatures.func_key_template_id == template_id,
UserFeatures.func_key_private_template_id == template_id,
)).filter(LineFeatures.endpoint == "sccp").exists())
return Session.query(exists_query).scalar()
def find_all_by_line_id(line_id):
return (Session.query(UserLine.line_id,
UserLine.extension_id)
.filter(UserLine.line_id == line_id)
.filter(UserLine.extension_id != None) # noqa
.distinct()
.all())
def find_main_endpoint_sip_by_user(user_uuid):
query = (Session.query(SIP).join(User.main_line_rel).join(
UserLine.main_line_rel).join(
Line.sip_endpoint).filter(User.uuid == str(user_uuid)))
return query.first()
def set_language(language):
row = Session.query(StaticSIP).filter(
StaticSIP.var_name == 'language').first()
row.var_val = language
Session.add(row)
row = Session.query(StaticIAX).filter(
StaticIAX.var_name == 'language').first()
row.var_val = language
Session.add(row)
row = (Session.query(SCCPGeneralSettings).filter(
SCCPGeneralSettings.option_name == 'language').first())
row.option_value = language
Session.add(row)
def get_associated_resource(extension_id):
row = (Session.query(Extension.type.label('resource'),
Extension.typeval.label('resource_id'))
.filter(Extension.id == extension_id)
.first())
return row.resource, row.resource_id
def _get_associator(self, extension):
context_type = (Session.query(Context.contexttype).filter(
Context.name == extension.context).scalar())
if context_type not in self.associators:
raise NotImplementedError(
"Cannot associate extension with context type '{}'".format(
context_type))
return self.associators[context_type]
def find_all_dst_user(user_id):
query = (Session.query(FuncKeyTemplate).join(
FuncKeyMapping, FuncKeyTemplate.id == FuncKeyMapping.template_id).join(
FuncKey, FuncKeyMapping.func_key_id == FuncKey.id).join(
FuncKeyDestUser,
FuncKey.id == FuncKeyDestUser.func_key_id).filter(
FuncKeyDestUser.user_id == user_id))
return query.all()
def update_ring_seconds(self, entry):
ring_seconds = entry.extract_field('incall', 'ring_seconds')
if ring_seconds:
user = entry.get_resource('user')
(Session.query(Dialaction).filter_by(
event='answer',
category='incall',
action='user',
actionarg1=str(user.id)).update(
{'actionarg2': str(ring_seconds)}))
def associate_sccp_device(line, device):
device_name = "SEP" + device.mac.replace(":", "").upper()
sccpdevice = (Session.query(SCCPDevice).filter(
SCCPDevice.device == device_name).first())
if sccpdevice:
sccpdevice.line = line.name
else:
Session.add(
SCCPDevice(name=device_name, device=device_name, line=line.name))
Session.flush()
def set_resolvconf(hostname, domain, nameservers):
row = Session.query(Resolvconf).first()
row.hostname = hostname
row.domain = domain
row.search = domain
row.description = 'Wizard Configuration'
row.nameserver1 = nameservers[0]
if len(nameservers) > 1:
row.nameserver2 = nameservers[1]
if len(nameservers) > 2:
row.nameserver3 = nameservers[2]
Session.add(row)
def sip_lines_for_device(device_id):
query = (Session.query(LineFeatures, UserSIP, Extension).join(
LineFeatures.endpoint_sip).join(LineFeatures.user_lines).join(
UserLine.main_user_rel).join(LineFeatures.line_extensions).join(
LineExtension.main_extension_rel).filter(
LineFeatures.device == device_id).options(
Load(LineFeatures).load_only("id", "configregistrar"),
Load(UserSIP).load_only("id", "callerid", "name",
"secret"),
Load(Extension).load_only("id", "exten"),
))
return query.all()
def export_query(separator=";"):
ordered_incalls = aliased(
Session.query(
Incall.exten.label('exten'),
Incall.context.label('context'),
User.id.label('user_id')
)
.join(Dialaction,
and_(Dialaction.category == 'incall',
Dialaction.action == 'user',
cast(Dialaction.categoryval, Integer) == Incall.id))
.join(User,
cast(Dialaction.actionarg1, Integer) == User.id)
.order_by(Incall.exten, Incall.context)
.subquery()
)
grouped_incalls = aliased(
Session.query(
ordered_incalls.c.user_id,
func.string_agg(ordered_incalls.c.exten, separator).label('exten'),
func.string_agg(ordered_incalls.c.context, separator).label('context')
)
.group_by(ordered_incalls.c.user_id)
.subquery()
)
ordered_call_permissions = aliased(
Session.query(
RightCall.name,
cast(RightCallMember.typeval, Integer).label('user_id')
)
.join(RightCallMember,
RightCallMember.rightcallid == RightCall.id)
.join(User,
and_(RightCallMember.type == 'user',
cast(RightCallMember.typeval, Integer) == User.id))
.order_by(RightCall.name)
.subquery()
)
grouped_call_permissions = aliased(
Session.query(
ordered_call_permissions.c.user_id,
func.string_agg(ordered_call_permissions.c.name, separator).label('name')
)
.group_by(ordered_call_permissions.c.user_id)
.subquery()
)
columns = (
User.uuid,
cast(User.entity_id, String),
User.firstname,
User.lastname,
User.email,
User.mobile_phone_number,
User.outgoing_caller_id,
User.language,
User.call_permission_password,
case([(User.enabled, '1')], else_='0'),
cast(User.ring_seconds, String),
cast(User.simultaneous_calls, String),
cast(User.enablehint, String),
cast(User.enablexfer, String),
cast(User.callrecord, String),
cast(User.enableonlinerec, String),
User.userfield,
User.username,
User.password,
CtiProfile.name,
cast(User.enableclient, String),
Voicemail.name,
Voicemail.number,
Voicemail.context,
Voicemail.password,
Voicemail.email,
cast(Voicemail.attach, String),
cast(Voicemail.deletevoicemail, String),
cast(cast(Voicemail.ask_password, Integer), String),
Line.endpoint,
Line.provisioning_code,
func.coalesce(Extension.context, Line.context),
SIP.name,
SIP.secret,
Extension.exten,
grouped_incalls.c.exten,
grouped_incalls.c.context,
grouped_call_permissions.c.name,
)
query = (
Session.query(*columns)
.outerjoin(User.voicemail)
.outerjoin(User.cti_profile)
.outerjoin(User.main_line_rel)
.outerjoin(UserLine.main_line_rel)
.outerjoin(Line.sip_endpoint)
.outerjoin(UserLine.extensions)
.outerjoin(grouped_incalls,
User.id == grouped_incalls.c.user_id)
.outerjoin(grouped_call_permissions,
User.id == grouped_call_permissions.c.user_id)
)
return COLUMNS, query
def set_timezone(timezone):
row = Session.query(General).first()
row.timezone = timezone
Session.add(row)
def find_all_timezone():
rows = (Session.query(StaticVoicemail.var_name).filter(
StaticVoicemail.category == 'zonemessages').all())
return [row.var_name for row in rows]
def find_by(**kwargs):
return Session.query(AgentLoginStatus).filter_by(**kwargs).first()
def set_admin_password(password):
row = Session.query(User).filter(User.login == 'root').first()
row.passwd = password
Session.add(row)
def entity_id_exists(entity_id):
query = (Session.query(Entity).filter_by(id=entity_id).exists())
return Session.query(query).scalar()
def export_query(tenant_uuid, separator=";"):
ordered_incalls = aliased(
Session.query(
Extension.exten.label('exten'),
Extension.context.label('context'),
User.id.label('user_id'),
).select_from(Incall).join(
Dialaction,
and_(
Dialaction.category == 'incall',
Dialaction.action == 'user',
cast(Dialaction.categoryval, Integer) == Incall.id,
),
).join(User,
cast(Dialaction.actionarg1, Integer) == User.id).join(
Extension,
and_(
Extension.type == 'incall',
cast(Extension.typeval, Integer) == Incall.id,
),
).order_by(Extension.exten, Extension.context).subquery())
grouped_incalls = aliased(
Session.query(
ordered_incalls.c.user_id,
func.string_agg(ordered_incalls.c.exten, separator).label('exten'),
func.string_agg(ordered_incalls.c.context,
separator).label('context'),
).group_by(ordered_incalls.c.user_id).subquery())
ordered_call_permissions = aliased(
Session.query(
RightCall.name,
cast(RightCallMember.typeval, Integer).label('user_id')).join(
RightCallMember,
RightCallMember.rightcallid == RightCall.id).join(
User,
and_(
RightCallMember.type == 'user',
cast(RightCallMember.typeval, Integer) == User.id,
),
).order_by(RightCall.name).subquery())
grouped_call_permissions = aliased(
Session.query(
ordered_call_permissions.c.user_id,
func.string_agg(ordered_call_permissions.c.name,
separator).label('name'),
).group_by(ordered_call_permissions.c.user_id).subquery())
columns = (
User.uuid,
User.subscription_type,
User.firstname,
User.lastname,
User.email,
User.mobile_phone_number,
User.outgoing_caller_id,
User.language,
User.call_permission_password,
case([(User.enabled, '1')], else_='0'),
cast(User.ring_seconds, String),
cast(User.simultaneous_calls, String),
cast(User.enablehint, String),
cast(User.enablexfer, String),
cast(User.dtmf_hangup, String),
case([(User.call_record_outgoing_external_enabled, '1')], else_='0'),
case([(User.call_record_outgoing_internal_enabled, '1')], else_='0'),
case([(User.call_record_incoming_external_enabled, '1')], else_='0'),
case([(User.call_record_incoming_internal_enabled, '1')], else_='0'),
cast(User.enableonlinerec, String),
User.userfield,
Voicemail.name,
Voicemail.number,
Voicemail.context,
Voicemail.password,
Voicemail.email,
cast(Voicemail.attach, String),
cast(Voicemail.deletevoicemail, String),
cast(cast(Voicemail.ask_password, Integer), String),
case(
[
(Line.endpoint_sip_uuid != None, 'sip'), # noqa
(Line.endpoint_sccp_id != None, 'sccp'), # noqa
(Line.endpoint_custom_id != None, 'custom'), # noqa
],
else_='',
),
Line.provisioning_code,
func.coalesce(Extension.context, Line.context),
EndpointSIP.username,
EndpointSIP.password,
Extension.exten,
grouped_incalls.c.exten,
grouped_incalls.c.context,
grouped_call_permissions.c.name,
)
query = (Session.query(*columns).outerjoin(User.voicemail).outerjoin(
User.main_line_rel).outerjoin(UserLine.main_line_rel).outerjoin(
Line.endpoint_sip).outerjoin(Line.line_extensions).outerjoin(
LineExtension.main_extension_rel).outerjoin(
grouped_incalls,
User.id == grouped_incalls.c.user_id).outerjoin(
grouped_call_permissions,
User.id == grouped_call_permissions.c.user_id).filter(
User.tenant_uuid == tenant_uuid))
return COLUMNS, query
def dissociate_sccp_device(line, device):
device_name = "SEP" + device.mac.replace(":", "").upper()
(Session.query(SCCPDevice).filter(SCCPDevice.device == device_name).filter(
SCCPDevice.line == line.name).delete())
Session.flush()
def get():
return Session.query(Provisioning).first()
def profile_for_device(device_id):
query = (Session.query(UserFeatures.uuid, LineFeatures.context).join(
LineFeatures.user_lines).join(UserLine.main_user_rel).filter(
LineFeatures.device_id == device_id).filter(LineFeatures.num == 1))
return query.first()
def find_line_id_for_user(user_id):
return (Session.query(
UserLine.user_id).filter_by(user_id=user_id).scalar())
def get():
return Session.query(Dhcp).first()
def set_xivo_configured():
row = Session.query(General).first()
row.configured = True
Session.add(row)
def find_extension_id_for_line(line_id):
return (Session.query(
UserLine.extension_id).filter(UserLine.line_id == line_id).filter(
UserLine.extension_id != None) # noqa
.scalar())
def get_xivo_configured():
return Session.query(General).first()
def update(dhcp_form):
dhcp = Session.query(Dhcp).first()
for name, value in dhcp_form.items():
setattr(dhcp, name, value)