def get_first_char_of_all_domains(conn=None):
"""Get first character of all domains."""
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
admin = session.get('username')
chars = []
try:
if sql_lib_general.is_global_admin(admin=admin, conn=conn):
qr = conn.select(
'domain',
what='SUBSTRING(domain FROM 1 FOR 1) AS first_char',
group='first_char')
else:
qr = conn.query(
"""SELECT SUBSTRING(domain.domain FROM 1 FOR 1) AS first_char
FROM domain
LEFT JOIN domain_admins ON (domain.domain=domain_admins.domain)
WHERE domain_admins.username=$admin
GROUP BY first_char""",
vars={'admin': admin})
if qr:
chars = [i.first_char.upper() for i in qr]
chars.sort()
return (True, chars)
except Exception as e:
logger.error(e)
return (False, repr(e))
def revoke_admin_privilege_if_no_managed_domains(admin=None, conn=None):
"""If given admin doesn't manage any domain, revoke the admin privilege.
@admin -- email address of domain admin
@conn -- sql connection cursor
"""
if not admin:
return (True, )
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
# Return immediately if it's a global admin
if sql_lib_general.is_global_admin(admin=admin, conn=conn):
return (True, )
if not num_managed_domains(admin=admin, conn=conn):
try:
conn.update('mailbox',
vars={'admin': admin},
isadmin=0,
where='username=$admin')
except Exception as e:
log_traceback()
return (False, repr(e))
return (True, )
def num_managed_users(admin=None, domains=None, conn=None, listed_only=False):
"""Count users of all managed domains."""
num = 0
if not admin:
admin = session.get('username')
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
if domains:
domains = [str(d).lower() for d in domains if iredutils.is_domain(d)]
else:
qr = get_managed_domains(conn=conn,
admin=admin,
domain_name_only=True,
listed_only=listed_only)
if qr[0]:
domains = qr[1]
if not domains:
return num
sql_vars = {'admin': admin, 'domains': domains}
try:
if sql_lib_general.is_global_admin(admin=admin, conn=conn):
if domains:
qr = conn.select('mailbox',
vars=sql_vars,
what='COUNT(username) AS total',
where='domain IN $domains')
else:
qr = conn.select('mailbox', what='COUNT(username) AS total')
else:
sql_append_where = ''
if domains:
sql_append_where = 'AND mailbox.domain IN %s' % web.sqlquote(
domains)
qr = conn.query(
"""
SELECT COUNT(mailbox.username) AS total
FROM mailbox
LEFT JOIN domain_admins ON (mailbox.domain = domain_admins.domain)
WHERE domain_admins.username=$admin %s
""" % (sql_append_where),
vars=sql_vars,
)
num = qr[0].total or 0
except:
log_traceback()
return num
def get_managed_domains(admin,
domain_name_only=False,
listed_only=False,
conn=None):
admin = str(admin).lower()
if not iredutils.is_email(admin):
return (False, 'INCORRECT_USERNAME')
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
try:
if sql_lib_general.is_global_admin(admin=admin, conn=conn):
if listed_only:
result = conn.query("""
SELECT domain.domain
FROM domain
LEFT JOIN domain_admins ON (domain.domain=domain_admins.domain)
WHERE domain_admins.username=$admin
ORDER BY domain_admins.domain
""",
vars={'admin': admin})
else:
result = conn.select('domain', what='domain', order='domain')
else:
sql_left_join = ''
if not listed_only:
sql_left_join = """OR domain_admins.domain='ALL'"""
result = conn.query("""
SELECT domain.domain
FROM domain
LEFT JOIN domain_admins ON (domain.domain=domain_admins.domain %s)
WHERE domain_admins.username=$admin
ORDER BY domain_admins.domain
""" % (sql_left_join),
vars={'admin': admin})
if domain_name_only:
domains = []
for i in result:
_domain = str(i['domain']).lower()
if iredutils.is_domain(_domain):
domains.append(_domain)
return (True, domains)
else:
return (True, list(result))
except Exception as e:
log_traceback()
return (False, repr(e))
def sum_all_used_quota(conn):
"""Sum all used quota. Return a dict: {'messages': x, 'bytes': x}."""
d = {'messages': 0, 'bytes': 0}
admin = session.get('username')
if sql_lib_general.is_global_admin(admin=admin, conn=conn):
qr = conn.query("""SELECT SUM(messages) AS messages,
SUM(bytes) AS bytes
FROM %s""" % settings.SQL_TBL_USED_QUOTA)
row = qr[0]
d['messages'] = row.messages
d['bytes'] = row.bytes
return d
def num_managed_domains(admin=None,
disabled_only=False,
first_char=None,
conn=None):
num = 0
if not admin:
admin = session.get('username')
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
sql_where = ''
if disabled_only is True:
sql_where += 'domain.active=0'
if first_char:
first_char = first_char[0].lower()
if sql_where:
sql_where += ' AND domain.domain LIKE %s' % web.sqlquote(
first_char + '%')
else:
sql_where += 'domain.domain LIKE %s' % web.sqlquote(first_char +
'%')
try:
if sql_lib_general.is_global_admin(admin=admin, conn=conn):
qr = conn.select('domain',
what='COUNT(domain) AS total',
where=sql_where or None)
else:
if sql_where:
sql_where = 'AND ' + sql_where
qr = conn.query("""
SELECT COUNT(domain.domain) AS total
FROM domain
LEFT JOIN domain_admins ON (domain.domain=domain_admins.domain)
WHERE domain_admins.username=$admin %s
""" % (sql_where),
vars={'admin': admin})
num = qr[0].total or 0
except:
log_traceback()
return num
def POST(self, profile_type, mail):
form = web.input(
enabledService=[],
shadowAddress=[],
telephoneNumber=[],
domainName=[], # Managed domains
)
mail = str(mail).lower()
domain = mail.split('@', 1)[-1]
if not session.get('is_admin') and not session.get('is_global_admin'):
mail = session.get("username")
form['accountStatus'] = '1'
if session.get('is_admin'):
#check if admin asking for details of correct domain
admins_of_domain = sql_lib_domain.get_domain_admin_addresses(
domain=domain)[1]
if session.get(
'username'
) not in admins_of_domain and not session.get('is_global_admin'):
raise web.seeother('/domains?msg=PERMISSION_DENIED')
_wrap = SQLWrap()
conn = _wrap.conn
#check if a non-global admin not trying to edit another admin
if session.get("is_global_admin", False) is False:
if sql_lib_general.is_global_admin(
admin=mail) or sql_lib_user.user_is_global_admin(
conn=conn,
mail=mail) or sql_lib_user.user_is_normal_admin(
conn=conn, mail=mail):
raise web.seeother('/domains?msg=PERMISSION_DENIED')
result = sql_lib_user.update(conn=conn,
mail=mail,
profile_type=profile_type,
form=form)
if result[0]:
raise web.seeother('/profile/user/{}/{}?msg=UPDATED'.format(
profile_type, mail))
else:
raise web.seeother('/profile/user/{}/{}?msg={}'.format(
profile_type, mail, web.urlquote(result[1])))
def __num_allocated_accounts(admin=None,
domains=None,
conn=None,
listed_only=False):
"""Count allocated users/aliases/lists of all managed domains."""
num = {'users': 0, 'aliases': 0, 'lists': 0}
if not admin:
admin = session.get('username')
if not conn:
_wrap = SQLWrap()
conn = _wrap.conn
if domains:
domains = [str(d).lower() for d in domains if iredutils.is_domain(d)]
else:
qr = get_managed_domains(conn=conn,
admin=admin,
domain_name_only=True,
listed_only=listed_only)
if qr[0]:
domains = qr[1]
if not domains:
return num
sql_vars = {'admin': admin, 'domains': domains}
try:
if sql_lib_general.is_global_admin(admin=admin, conn=conn):
sql_what = 'SUM(mailboxes) AS mailboxes, SUM(aliases) AS aliases, SUM(maillists) AS maillists'
if domains:
qr = conn.select('domain',
vars=sql_vars,
what=sql_what,
where='domain IN $domains')
else:
qr = conn.select('domain', what=sql_what)
else:
sql_what = 'SUM(domain.mailboxes) AS mailboxes, SUM(domain.aliases) AS aliases, SUM(domain.maillists) as maillists'
sql_append_where = ''
if domains:
sql_append_where = 'AND domain.domain IN %s' % web.sqlquote(
domains)
qr = conn.query("""
SELECT %s
FROM domain
LEFT JOIN domain_admins ON (domain.domain = domain_admins.domain)
WHERE domain_admins.username=$admin %s
""" % (sql_what, sql_append_where),
vars=sql_vars)
if qr:
_qr = list(qr)[0]
num['users'] = int(_qr.mailboxes) or 0
except:
log_traceback()
return num
def GET(self, profile_type, mail):
mail = str(mail).lower()
form = web.input()
if not (session.get('is_global_admin')
or session.get('username') == mail):
# Don't allow to view/update others' profile.
raise web.seeother(
'/profile/admin/general/%s?msg=PERMISSION_DENIED' %
session.get('username'))
_wrap = SQLWrap()
conn = _wrap.conn
is_global_admin = sql_lib_general.is_global_admin(admin=mail,
conn=conn)
result = sql_lib_admin.get_profile(mail=mail, conn=conn)
if result[0] is True:
profile = result[1]
qr = sql_lib_general.get_admin_settings(admin=mail, conn=conn)
if qr[0]:
admin_settings = qr[1]
else:
return qr
# Get all domains.
all_domains = []
qr_all_domains = sql_lib_domain.get_all_domains(conn=conn)
if qr_all_domains[0] is True:
all_domains = qr_all_domains[1]
# Get managed domains.
managed_domains = []
qr = sql_lib_admin.get_managed_domains(conn=conn,
admin=mail,
domain_name_only=True,
listed_only=True)
if qr[0] is True:
managed_domains += qr[1]
return web.render(
'sql/admin/profile.html',
mail=mail,
profile_type=profile_type,
is_global_admin=is_global_admin,
profile=profile,
admin_settings=admin_settings,
languagemaps=iredutils.get_language_maps(),
allDomains=all_domains,
managedDomains=managed_domains,
min_passwd_length=settings.min_passwd_length,
max_passwd_length=settings.max_passwd_length,
store_password_in_plain_text=settings.
STORE_PASSWORD_IN_PLAIN_TEXT,
password_policies=iredutils.get_password_policies(),
msg=form.get('msg'),
)
else:
# Return to user profile page if admin is a mail user.
qr = sql_lib_user.simple_profile(conn=conn,
mail=mail,
columns=['username'])
if qr[0]:
raise web.seeother('/profile/user/general/' + mail)
else:
raise web.seeother('/admins?msg=' + web.urlquote(result[1]))