iredadmin-ose / libs / pgsql / domain.py

# Author: Zhang Huangbin <zhb@iredmail.org>

import web
from libs import iredutils, settings
from libs.pgsql import core, decorators, connUtils

cfg = web.iredconfig
session = web.config.get('_session')


class Domain(core.PGSQLWrap):
    def __del__(self):
        pass

    @decorators.require_global_admin
    def enableOrDisableAccount(self, accounts, active=True):
        return self.setAccountStatus(accounts=accounts, active=active, accountType='domain',)

    def getAllDomains(self, columns=[],):
        """Get all domains. Return (True, [records])."""
        try:
            if columns:
                result = self.conn.select('domain', what=','.join(columns),)
            else:
                result = self.conn.select('domain')

            return (True, list(result))
        except Exception, e:
            return (False, str(e))

    def getDomainAdmins(self, domain, mailOnly=False):
        domain = str(domain)

        if not iredutils.isDomain(domain):
            return (False, 'INVALID_DOMAIN_NAME')

        try:
            qr = self.conn.query(
                '''
                SELECT
                    admin.username, admin.name, admin.language, admin.active
                FROM admin
                LEFT JOIN domain_admins ON (domain_admins.username=admin.username)
                WHERE domain_admins.domain=$domain
                ''',
                vars={'domain': domain, },
            )

            if mailOnly is True:
                admins = []
                for adm in qr:
                    admins += [adm.username]
                return (True, admins)
            else:
                return (True, list(qr))
        except Exception, e:
            return (False, str(e))

    @decorators.require_domain_access
    def getCountsOfExistAccountsUnderDomain(self, domain, accountType='user'):
        if not iredutils.isDomain(domain):
            return (False, 'INVALID_DOMAIN_NAME')

        sql_vars = {'domain': domain, }

        if accountType == 'user':
            try:
                qr1 = self.conn.select(
                    'mailbox',
                    vars=sql_vars,
                    what='COUNT(username) AS mailbox_count',
                    where='domain=$domain',
                )
                mailbox_count = qr1[0].mailbox_count or 0

                qr2 = self.conn.select(
                    'mailbox',
                    vars=sql_vars,
                    what='SUM(quota) AS quota_count',
                    where='domain=$domain',
                )
                quota_count = qr2[0].quota_count or 0
                return (True, mailbox_count, quota_count)
            except Exception, e:
                return (False, str(e))
        elif accountType == 'alias':
            try:
                result = self.conn.select(
                    'alias',
                    vars=sql_vars,
                    what='COUNT(address) AS alias_count',
                    where='domain = $domain AND address <> goto',
                )
                result = list(result)
                return (True, result[0].alias_count)
            except Exception, e:
                return (False, str(e))
        else:
            return (False, 'INVALID_ACCOUNT_TYPE')

    @decorators.require_domain_access
    def getAllocatedQuotaSize(self, domain):
        try:
            result = self.conn.select(
                'mailbox',
                vars={'domain': domain, },
                what='SUM(quota) AS total',
                where='domain = $domain',
            )
            result = list(result)
            return (True, result[0].total or 0)
        except Exception, e:
            return (False, str(e))

    # List all domains under control.
    def listAccounts(self, cur_page=1):
        admin = session.get('username')

        page = int(cur_page) or 1

        sql_where = ''
        if session.get('domainGlobalAdmin') is not True:
            sql_where = ' WHERE domain_admins.username = %s' % web.sqlquote(admin)

        # RAW sql command used to get records.
        rawSQLOfRecords = """
            SELECT
                a.domain, a.description, a.aliases, a.mailboxes, a.maxquota, a.quota,
                a.transport, a.backupmx, a.active,
                NULLIF(b.alias_count, 0) AS alias_count,
                NULLIF(c.mailbox_count, 0) AS mailbox_count,
                NULLIF(c.quota_count, 0) AS quota_count
            FROM domain AS a
            LEFT JOIN (
                SELECT domain, COUNT(address) AS alias_count
                FROM alias
                WHERE
                    address<>goto
                    AND address<>domain
                    AND address NOT IN (SELECT username FROM mailbox)
                GROUP BY domain
                ) AS b ON (a.domain=b.domain)
            LEFT JOIN (
                SELECT domain,
                    SUM(mailbox.quota) AS quota_count,
                    COUNT(username) AS mailbox_count
                FROM mailbox
                GROUP BY domain
                ) AS c ON (a.domain=c.domain)
            LEFT JOIN domain_admins ON (domain_admins.domain=a.domain)
            %s
            GROUP BY
                a.domain, a.description, a.aliases, a.mailboxes, a.maxquota, a.quota,
                a.transport, a.backupmx, a.active,
                mailbox_count, alias_count, quota_count
            ORDER BY a.domain
            LIMIT %d
            OFFSET %d
        """ % (sql_where, settings.PAGE_SIZE_LIMIT, (page - 1) * settings.PAGE_SIZE_LIMIT,)

        if self.isGlobalAdmin(admin):
            try:
                resultOfTotal = self.conn.select(
                    'domain',
                    what='COUNT(domain) AS total',
                )

                resultOfRecords = self.conn.query(rawSQLOfRecords)
            except Exception, e:
                return (False, str(e))
        else:
            try:
                resultOfTotal = self.conn.select(
                    ['domain', 'domain_admins', ],
                    vars={'admin': admin, },
                    what='COUNT(domain.domain) AS total',
                    where='domain.domain = domain_admins.domain AND domain_admins.username = $admin',
                )

                resultOfRecords = self.conn.query(rawSQLOfRecords)
            except Exception, e:
                return (False, str(e))

        if len(resultOfTotal) == 1:
            total = resultOfTotal[0].total or 0
        else:
            total = 0

        return (True, total, list(resultOfRecords),)

    @decorators.require_global_admin
    def delete(self, domains=[]):
        if not isinstance(domains, list):
            return (False, 'INVALID_DOMAIN_NAME')

        domains = [str(v).lower()
                   for v in domains
                   if iredutils.isDomain(v)
                  ]

        if not domains:
            return (True, )

        sql_vars = {'domains': domains, }

        # Delete domain and related records.
        try:
            self.conn.delete('domain', vars=sql_vars, where='domain IN $domains', )

            self.conn.delete(
                'alias_domain',
                vars=sql_vars,
                where='alias_domain IN $domains OR target_domain IN $domains',
            )

            for tbl in ['alias', 'domain_admins', 'mailbox', \
                        'recipient_bcc_domain', 'recipient_bcc_user', \
                        'sender_bcc_domain', 'sender_bcc_user', \
                       ]:
                self.conn.delete(
                    tbl,
                    vars=sql_vars,
                    where='domain IN $domains',
                )

            # Delete real-time mailbox quota.
            try:
                self.conn.query(
                    'DELETE FROM used_quota WHERE %s' % \
                    web.sqlors('username LIKE ', ['%@' + d for d in domains])
                )
            except:
                pass

            for d in domains:
                web.logger(msg="Delete domain: %s." % (d), domain=d, event='delete',)
            return (True,)
        except Exception, e:
            return (False, str(e))

    @decorators.require_domain_access
    def simpleProfile(self, domain, columns=[]):
        domain = web.safestr(domain)

        if not iredutils.isDomain(domain):
            return (False, 'INVALID_DOMAIN_NAME')

        try:
            qr = self.conn.select('domain',
                                  vars={'domain': domain, },
                                  what=','.join(columns) or '*',
                                  where='domain=$domain',
                                 )

            if len(qr) == 1:
                # Return first list element.
                return (True, list(qr)[0])
            else:
                return (False, 'NO_SUCH_OBJECT')
        except Exception, e:
            return (False, str(e))

    @decorators.require_domain_access
    def profile(self, domain):
        domain = web.safestr(domain)

        if not iredutils.isDomain(domain):
            return (False, 'INVALID_DOMAIN_NAME')

        try:
            qr = self.conn.query(
                '''
                SELECT
                    domain.domain, domain.description, domain.aliases,
                    domain.mailboxes, domain.maxquota, domain.quota,
                    domain.transport, domain.backupmx, domain.active,
                    domain.defaultuseraliases, domain.defaultuserquota,
                    domain.minpasswordlength, domain.maxpasswordlength,
                    sbcc.bcc_address AS sbcc_addr,
                    sbcc.active AS sbcc_active,
                    rbcc.bcc_address AS rbcc_addr,
                    rbcc.active AS rbcc_active,
                    alias.goto AS catchall,
                    alias.active AS catchall_active,
                    COUNT(DISTINCT mailbox.username) AS mailbox_count,
                    COUNT(DISTINCT alias.address) AS alias_count
                FROM domain
                LEFT JOIN sender_bcc_domain AS sbcc ON (sbcc.domain=domain.domain)
                LEFT JOIN recipient_bcc_domain AS rbcc ON (rbcc.domain=domain.domain)
                LEFT JOIN domain_admins ON (domain.domain = domain_admins.domain)
                LEFT JOIN mailbox ON (domain.domain = mailbox.domain)
                LEFT JOIN alias ON (
                    domain.domain = alias.address
                    AND alias.address <> alias.goto
                    )
                WHERE domain.domain=$domain
                GROUP BY
                    domain.domain, domain.description, domain.aliases,
                    domain.mailboxes, domain.maxquota, domain.quota,
                    domain.transport, domain.backupmx, domain.active,
                    domain.defaultuseraliases, domain.defaultuserquota,
                    domain.minpasswordlength, domain.maxpasswordlength,
                    sbcc.bcc_address, sbcc.active,
                    rbcc.bcc_address, rbcc.active,
                    alias.goto, alias.active
                ORDER BY domain.domain
                LIMIT 1
                ''',
                vars={'domain': domain, },
            )

            if len(qr) == 1:
                # Return first list element.
                return (True, list(qr)[0])
            else:
                return (False, 'NO_SUCH_OBJECT')
        except Exception, e:
            return (False, str(e))

    @decorators.require_global_admin
    def add(self, data):
        domain = web.safestr(data.get('domainName', '')).strip().lower()

        # Get company/organization name.
        cn = data.get('cn', '')

        # Check domain name.
        if not iredutils.isDomain(domain):
            return (False, 'INVALID_DOMAIN_NAME')

        # Check whether domain name already exist (domainName, domainAliasName).
        connutils = connUtils.Utils()
        if connutils.isDomainExists(domain):
            return (False, 'ALREADY_EXISTS')

        # Add domain in database.
        try:
            self.conn.insert(
                'domain',
                domain=domain,
                description=cn,
                transport=cfg.general.get('transport', 'dovecot'),
                created=iredutils.getGMTTime(),
                active='1',
            )
            web.logger(msg="Create domain: %s." % (domain), domain=domain, event='create',)
        except Exception, e:
            return (False, str(e))

        return (True,)

    @decorators.require_domain_access
    def update(self, domain, profile_type, data,):
        profile_type = str(profile_type)
        domain = str(domain)

        # Pre-defined update key:value.
        updates = {'modified': iredutils.getGMTTime(), }

        sql_vars = {'domain': domain, }

        if profile_type == 'general':
            # Get name
            cn = data.get('cn', '')
            updates['description'] = cn

            if session.get('domainGlobalAdmin') is True:
                # Get account status
                if 'accountStatus' in data.keys():
                    updates['active'] = 1
                else:
                    updates['active'] = 0

                # Update SQL db with columns: maxquota, active.
                try:
                    self.conn.update(
                        'domain',
                        vars=sql_vars,
                        where='domain=$domain',
                        **updates
                    )
                except Exception, e:
                    return (False, str(e))

        web.logger(msg="Update domain profile: %s (%s)." % (domain, profile_type),
                   domain=domain,
                   event='update',
                  )

        return (True,)
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.