3 Added temporary tables creation queries
source | link
CREATE TEMPORARY TABLE IF NOT EXISTS temp1 AS SELECT
        COUNT(rm_users.username) AS expired_users,
        owner 
    FROM
        rm_users 
    LEFT JOIN
        `rm_services` 
            ON rm_users.srvid = rm_services.srvid 
    WHERE
        1 = 1 
        AND rm_users.acctype = '0' 
        AND rm_users.enableuser = '1' 
        AND (
            if(rm_services.limitdl = 1, rm_users.downlimit - (SELECT
                COALESCE(SUM(radacct.acctoutputoctets),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) - (SELECT
                COALESCE(SUM(dlbytes),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) <= 0 
            OR if(rm_services.limitul = 1, rm_users.uplimit - (SELECT
                COALESCE(SUM(radacct.acctinputoctets),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) - (SELECT
                COALESCE(SUM(ulbytes),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) <= 0 
            OR if(rm_services.limitcomb = 1, rm_users.comblimit - (SELECT
                COALESCE(SUM(radacct.acctinputoctets + radacct.acctoutputoctets),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) + (SELECT
                COALESCE(SUM(ulbytes + dlbytes),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) <= 0 
            OR if(rm_services.limituptime = 1, rm_users.uptimelimit - (SELECT
                COALESCE(SUM(radacct.acctsessiontime),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) + (SELECT
                COALESCE(SUM(radacct.acctsessiontime),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) <= 0 
            OR if(rm_services.limitexpiration = 1, UNIX_TIMESTAMP(rm_users.expiration) - UNIX_TIMESTAMP(NOW()), 1) <= 0
        ) 
    GROUP BY
        owner 
    ORDER BY
        NULL

CREATE TEMPORARY TABLE IF NOT EXISTS temp2 AS SELECT
        COUNT(rm_users.username) AS active_users,
        owner 
    FROM
        `rm_users` 
    LEFT JOIN
        `rm_services` 
            ON rm_users.srvid = rm_services.srvid 
    WHERE
        1 = 1 
        AND rm_users.enableuser = '1' 
        AND rm_users.acctype = '0' 
        AND (
            rm_services.limitdl = 0 
            OR if(rm_services.limitdl = 1, rm_users.downlimit - (SELECT
                COALESCE(SUM(radacct.acctoutputoctets),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) - (SELECT
                COALESCE(SUM(rm_radacct.dlbytes),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) > 0
        ) 
        AND (
            rm_services.limitul = 0 
            OR if(rm_services.limitul = 1, rm_radacct.uplimit - (SELECT
                COALESCE(SUM(radacct.acctinputoctets),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) - (SELECT
                COALESCE(SUM(ulbytes),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) > 0
        ) 
        AND (
            rm_services.limitcomb = 0 
            OR if(rm_services.limitcomb = 1, rm_radacct.comblimit - (SELECT
                COALESCE(SUM(radacct.acctinputoctets + radacct.acctoutputoctets),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) + (SELECT
                COALESCE(SUM(rm_radacct.ulbytes + rm_radacct.dlbytes),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) > 0
        ) 
        AND (
            rm_services.limituptime = 0 
            OR if(rm_services.limituptime = 1, rm_radacct.uptimelimit - (SELECT
                COALESCE(SUM(radacct.acctsessiontime),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) - (SELECT
                COALESCE(SUM(radacct.acctsessiontime),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) > 0
        ) 
        AND (
            rm_services.limitexpiration = 0 
            OR if(rm_radacct.limitexpiration = 1, UNIX_TIMESTAMP(rm_radacct.expiration) - UNIX_TIMESTAMP(NOW()), 1) > 0
        ) 
    GROUP BY
        owner 
    ORDER BY
        NULL

CREATE TEMPORARY TABLE IF NOT EXISTS temp3 AS SELECT
        COUNT(rm_users.username) AS disabled_users,
        owner 
    FROM
        `rm_users` 
    WHERE
        1 = 1 
        AND rm_users.acctype = '0' 
        AND rm_users.enableuser = '0' 
    GROUP BY
        owner 
    ORDER BY
        NULL

SELECT
        temp1.expired_users,
        temp2.active_users,
        (SELECT
            COUNTrm_phpsess.ip 
        FROM
            rm_phpsess 
        WHERE
            rm_managers.managername = rm_phpsess.managername 
        ORDER BY
            rm_phpsess.lastact DESC LIMIT 1) AS ipip,
        temp3.disabled_users,
        (rm_usersSELECT
            (SUM(rm_services.usernameunitprice) + SUM(rm_services.unitpricetax)) 
        FROM
            `rm_users` 
        LEFT JOIN
            `rm_services` 
                ON rm_users.srvid = rm_services.srvid 
        WHERE
            rm_users.owner = rm_managers.managername 
            AND rm_users.acctype = '0' 
            AND rm_users.enableuser = '1' 
         expiration > '$date') ANDAS (sum_users,
                if(rm_servicesrm_managers.limitdl = 1firstname, rm_users.downlimit - (SELECT
                    COALESCE(SUM(acctoutputoctets)rm_managers.lastname,
                    0) 
                FROM
                    radacct 
                WHERE
                    radacct.username = rm_usersrm_managers.username) - (SELECT
                    COALESCE(SUM(dlbytes)managername,
                    0) 
                FROM
                    rm_radacct 
                WHERE
                    rm_radacct.username = rm_usersrm_managers.username)balance, 1) <= 0 
                OR if(rm_servicesrm_managers.limitul = 1comment, rm_users.uplimit - (SELECT
                    COALESCE(SUM(acctinputoctets)rm_managers.enablemanager,
                    0) 
                FROM
                    radacct 
                WHERE
                    radacct.username = rm_usersrm_managers.username) - (SELECT
                    COALESCE(SUM(ulbytes)country,
                    0) 
                FROM
                    rm_radacct 
                WHERE
                    rm_radacct.username = rm_usersrm_managers.username)city, 1) <= 0 
                OR if(rm_servicesrm_managers.limitcomb = 1state, rm_users.comblimit - (SELECT
                    COALESCE(SUM(acctinputoctets + acctoutputoctets)rm_managers.email,
                    0) 
                FROM
                    radacct 
                WHERE
                    radacct.username = rm_usersrm_managers.username) + (SELECT
                    COALESCE(SUM(ulbytes + dlbytes)zip,
                    0) 
                FROM
                    rm_radacct 
                WHERE
                    rm_radacct.username = rm_usersrm_managers.username)address, 1) <= 0 
                OR if(rm_servicesrm_managers.limituptime = 1company, rm_users.uptimelimit - (SELECT
                    COALESCE(SUM(acctsessiontime)rm_managers.phone,
                    0) 
                FROMrm_managers.mobile,
                    radacct rm_managergroups.mangroupid,
                WHERErm_managergroups.mangroupname,
                    radacct.username = rm_userst2.username) + (SELECTlastseen,
                    COALESCE(SUM(acctsessiontime)t2.onlinecount,
                    0)t2.lastip 
                FROM
                    rm_radacct`rm_managers` 
               LEFT WHEREJOIN
                    rm_radacct.username = rm_users.username), 1) <= 0`rm_managergroups` 
                ORON if(rm_servicesrm_managergroups.limitexpirationmangroupid = 1, UNIX_TIMESTAMP(rm_usersrm_managers.expiration) - UNIX_TIMESTAMP(NOW()), 1) <=mangroupid 0
          LEFT OUTER )JOIN
        ) AS expired_users, (
            SELECT
                rm_phpsess.managername,
                MAX(rm_phpsess.lastact) AS lastseen,
                (COUNT(rm_usersif(rm_phpsess.usernameclosed = '0',
                1,
                NULL))) AS onlinecount,
                (rm_phpsess.ip) AS lastip 
            FROM
                `rm_users` 
            LEFT JOIN
                `rm_services` 
                    ON rm_users.srvid = rm_services.srvid 
            WHERE
                owner = rm_managers.managername 
                AND enableuser = '1' 
                AND acctype = '0' 
                AND (
                    rm_services.limitdl = 0 
                    OR if(rm_services.limitdl = 1, downlimit - (SELECT
                        COALESCE(SUM(acctoutputoctets),
                        0) 
                    FROM
                        radacct 
                    WHERE
                        radacct.username = rm_users.username) - (SELECT
                        COALESCE(SUM(dlbytes),
                        0) 
                    FROM
                        rm_radacct 
                    WHERE
                        rm_radacct.username = rm_users.username), 1) > 0
                ) 
                AND (
                    rm_services.limitul = 0 
                    OR if(rm_services.limitul = 1, uplimit - (SELECT
                        COALESCE(SUM(acctinputoctets),
                        0) 
                    FROM
                        radacct 
                    WHERE
                        radacct.username = rm_users.username) - (SELECT
                        COALESCE(SUM(ulbytes),
                        0) 
                    FROM
                        rm_radacct 
                    WHERE
                        rm_radacct.username = rm_users.username), 1) > 0
                ) 
                AND (
                    rm_services.limitcomb = 0 
                    OR if(rm_services.limitcomb = 1, comblimit - (SELECT
                        COALESCE(SUM(acctinputoctets + acctoutputoctets),
                        0) 
                    FROM
                        radacct 
                    WHERE
                        radacct.username = rm_users.username) + (SELECT
                        COALESCE(SUM(ulbytes + dlbytes),
                        0) 
                    FROM
                        rm_radacct 
                    WHERE
                        rm_radacct.username = rm_users.username), 1) > 0
                ) 
                AND (
                    rm_services.limituptime = 0 
                    OR if(rm_services.limituptime = 1, uptimelimit - (SELECT
                        COALESCE(SUM(acctsessiontime),
                        0) 
                    FROM
                        radacct 
                    WHERE
                        radacct.username = rm_users.username) - (SELECT
                        COALESCE(SUM(acctsessiontime),
                        0) 
                    FROM
                        rm_radacct 
                    WHERE
                        rm_radacct.username = rm_users.username), 1) > 0
                ) 
                AND (
                    rm_services.limitexpiration = 0 
                    OR if(limitexpiration = 1, UNIX_TIMESTAMP(expiration) - UNIX_TIMESTAMP(NOW()), 1) > 0
                )
            ) AS active_users, (
                SELECT
                    ip 
                FROM
                    rm_phpsess`rm_phpsess` 
               GROUP WHEREBY
                    rm_managers.managername = rm_phpsess.managername 
                ORDER BY
                    rm_phpsess.lastact DESC LIMIT 1
            ) AS ipip, (
                SELECT
                    COUNT(username) 
                FROM
                    `rm_users` 
               t2 WHERE
                    owner =ON rm_managerst2.managername 
                    AND acctype = '0' 
                    AND enableuser = '0'
            ) AS disabled_users, (
                SELECT
                    (SUM(rm_services.unitprice) + SUM(rm_servicesrm_managers.unitpricetax)) 
                FROM
                    `rm_users`managername 
                LEFT JOIN
                    `rm_services`temp1 
                        ON rm_users.srvid = rm_services.srvid 
                WHERE
                    rm_userstemp1.owner = rm_managers.managername 
                    AND rm_users.acctype = '0' 
                    AND rm_users.expiration > '$date'
            ) AS sum_users, rm_managers.firstname, rm_managers.lastname, rm_managers.managername, rm_managers.balance, rm_managers.comment, rm_managers.enablemanager, rm_managers.country, rm_managers.city, rm_managers.state, rm_managers.email, rm_managers.zip, rm_managers.address, rm_managers.company, rm_managers.phone, rm_managers.mobile, rm_managergroups.mangroupid, rm_managergroups.mangroupname, t2.lastseen, t2.onlinecount, t2.lastip 
        FROM
            `rm_managers` 
        LEFT JOIN
            `rm_managergroups`temp2 
                ON rm_managergroupstemp2.mangroupidowner = rm_managers.mangroupidmanagername 
        LEFT OUTER JOIN
            (
                SELECT
                    rm_phpsess.managername,
                    MAX(rm_phpsess.lastact) AS lastseen,
                    (COUNT(if(closed = '0',
                    1,
                    NULL))) AS onlinecount,
                    (ip) AS lastip 
                FROM
                    `rm_phpsess` 
                GROUP BY
                    rm_phpsess.managername 
                ORDER BY
                    lastact DESC
            ) t2temp3 
                ON t2temp3.managernameowner = rm_managers.managername
SELECT
        (SELECT
            COUNT(rm_users.username) 
        FROM
            `rm_users` 
        LEFT JOIN
            `rm_services` 
                ON rm_users.srvid = rm_services.srvid 
        WHERE
            rm_users.owner = rm_managers.managername 
            AND rm_users.acctype = '0' 
            AND rm_users.enableuser = '1' 
            AND (
                if(rm_services.limitdl = 1, rm_users.downlimit - (SELECT
                    COALESCE(SUM(acctoutputoctets),
                    0) 
                FROM
                    radacct 
                WHERE
                    radacct.username = rm_users.username) - (SELECT
                    COALESCE(SUM(dlbytes),
                    0) 
                FROM
                    rm_radacct 
                WHERE
                    rm_radacct.username = rm_users.username), 1) <= 0 
                OR if(rm_services.limitul = 1, rm_users.uplimit - (SELECT
                    COALESCE(SUM(acctinputoctets),
                    0) 
                FROM
                    radacct 
                WHERE
                    radacct.username = rm_users.username) - (SELECT
                    COALESCE(SUM(ulbytes),
                    0) 
                FROM
                    rm_radacct 
                WHERE
                    rm_radacct.username = rm_users.username), 1) <= 0 
                OR if(rm_services.limitcomb = 1, rm_users.comblimit - (SELECT
                    COALESCE(SUM(acctinputoctets + acctoutputoctets),
                    0) 
                FROM
                    radacct 
                WHERE
                    radacct.username = rm_users.username) + (SELECT
                    COALESCE(SUM(ulbytes + dlbytes),
                    0) 
                FROM
                    rm_radacct 
                WHERE
                    rm_radacct.username = rm_users.username), 1) <= 0 
                OR if(rm_services.limituptime = 1, rm_users.uptimelimit - (SELECT
                    COALESCE(SUM(acctsessiontime),
                    0) 
                FROM
                    radacct 
                WHERE
                    radacct.username = rm_users.username) + (SELECT
                    COALESCE(SUM(acctsessiontime),
                    0) 
                FROM
                    rm_radacct 
                WHERE
                    rm_radacct.username = rm_users.username), 1) <= 0 
                OR if(rm_services.limitexpiration = 1, UNIX_TIMESTAMP(rm_users.expiration) - UNIX_TIMESTAMP(NOW()), 1) <= 0
            )
        ) AS expired_users, (
            SELECT
                COUNT(rm_users.username) 
            FROM
                `rm_users` 
            LEFT JOIN
                `rm_services` 
                    ON rm_users.srvid = rm_services.srvid 
            WHERE
                owner = rm_managers.managername 
                AND enableuser = '1' 
                AND acctype = '0' 
                AND (
                    rm_services.limitdl = 0 
                    OR if(rm_services.limitdl = 1, downlimit - (SELECT
                        COALESCE(SUM(acctoutputoctets),
                        0) 
                    FROM
                        radacct 
                    WHERE
                        radacct.username = rm_users.username) - (SELECT
                        COALESCE(SUM(dlbytes),
                        0) 
                    FROM
                        rm_radacct 
                    WHERE
                        rm_radacct.username = rm_users.username), 1) > 0
                ) 
                AND (
                    rm_services.limitul = 0 
                    OR if(rm_services.limitul = 1, uplimit - (SELECT
                        COALESCE(SUM(acctinputoctets),
                        0) 
                    FROM
                        radacct 
                    WHERE
                        radacct.username = rm_users.username) - (SELECT
                        COALESCE(SUM(ulbytes),
                        0) 
                    FROM
                        rm_radacct 
                    WHERE
                        rm_radacct.username = rm_users.username), 1) > 0
                ) 
                AND (
                    rm_services.limitcomb = 0 
                    OR if(rm_services.limitcomb = 1, comblimit - (SELECT
                        COALESCE(SUM(acctinputoctets + acctoutputoctets),
                        0) 
                    FROM
                        radacct 
                    WHERE
                        radacct.username = rm_users.username) + (SELECT
                        COALESCE(SUM(ulbytes + dlbytes),
                        0) 
                    FROM
                        rm_radacct 
                    WHERE
                        rm_radacct.username = rm_users.username), 1) > 0
                ) 
                AND (
                    rm_services.limituptime = 0 
                    OR if(rm_services.limituptime = 1, uptimelimit - (SELECT
                        COALESCE(SUM(acctsessiontime),
                        0) 
                    FROM
                        radacct 
                    WHERE
                        radacct.username = rm_users.username) - (SELECT
                        COALESCE(SUM(acctsessiontime),
                        0) 
                    FROM
                        rm_radacct 
                    WHERE
                        rm_radacct.username = rm_users.username), 1) > 0
                ) 
                AND (
                    rm_services.limitexpiration = 0 
                    OR if(limitexpiration = 1, UNIX_TIMESTAMP(expiration) - UNIX_TIMESTAMP(NOW()), 1) > 0
                )
            ) AS active_users, (
                SELECT
                    ip 
                FROM
                    rm_phpsess 
                WHERE
                    rm_managers.managername = rm_phpsess.managername 
                ORDER BY
                    lastact DESC LIMIT 1
            ) AS ipip, (
                SELECT
                    COUNT(username) 
                FROM
                    `rm_users` 
                WHERE
                    owner = rm_managers.managername 
                    AND acctype = '0' 
                    AND enableuser = '0'
            ) AS disabled_users, (
                SELECT
                    (SUM(rm_services.unitprice) + SUM(rm_services.unitpricetax)) 
                FROM
                    `rm_users` 
                LEFT JOIN
                    `rm_services` 
                        ON rm_users.srvid = rm_services.srvid 
                WHERE
                    rm_users.owner = rm_managers.managername 
                    AND rm_users.acctype = '0' 
                    AND rm_users.expiration > '$date'
            ) AS sum_users, rm_managers.firstname, rm_managers.lastname, rm_managers.managername, rm_managers.balance, rm_managers.comment, rm_managers.enablemanager, rm_managers.country, rm_managers.city, rm_managers.state, rm_managers.email, rm_managers.zip, rm_managers.address, rm_managers.company, rm_managers.phone, rm_managers.mobile, rm_managergroups.mangroupid, rm_managergroups.mangroupname, t2.lastseen, t2.onlinecount, t2.lastip 
        FROM
            `rm_managers` 
        LEFT JOIN
            `rm_managergroups` 
                ON rm_managergroups.mangroupid = rm_managers.mangroupid 
        LEFT OUTER JOIN
            (
                SELECT
                    rm_phpsess.managername,
                    MAX(rm_phpsess.lastact) AS lastseen,
                    (COUNT(if(closed = '0',
                    1,
                    NULL))) AS onlinecount,
                    (ip) AS lastip 
                FROM
                    `rm_phpsess` 
                GROUP BY
                    rm_phpsess.managername 
                ORDER BY
                    lastact DESC
            ) t2 
                ON t2.managername = rm_managers.managername
CREATE TEMPORARY TABLE IF NOT EXISTS temp1 AS SELECT
        COUNT(rm_users.username) AS expired_users,
        owner 
    FROM
        rm_users 
    LEFT JOIN
        `rm_services` 
            ON rm_users.srvid = rm_services.srvid 
    WHERE
        1 = 1 
        AND rm_users.acctype = '0' 
        AND rm_users.enableuser = '1' 
        AND (
            if(rm_services.limitdl = 1, rm_users.downlimit - (SELECT
                COALESCE(SUM(radacct.acctoutputoctets),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) - (SELECT
                COALESCE(SUM(dlbytes),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) <= 0 
            OR if(rm_services.limitul = 1, rm_users.uplimit - (SELECT
                COALESCE(SUM(radacct.acctinputoctets),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) - (SELECT
                COALESCE(SUM(ulbytes),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) <= 0 
            OR if(rm_services.limitcomb = 1, rm_users.comblimit - (SELECT
                COALESCE(SUM(radacct.acctinputoctets + radacct.acctoutputoctets),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) + (SELECT
                COALESCE(SUM(ulbytes + dlbytes),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) <= 0 
            OR if(rm_services.limituptime = 1, rm_users.uptimelimit - (SELECT
                COALESCE(SUM(radacct.acctsessiontime),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) + (SELECT
                COALESCE(SUM(radacct.acctsessiontime),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) <= 0 
            OR if(rm_services.limitexpiration = 1, UNIX_TIMESTAMP(rm_users.expiration) - UNIX_TIMESTAMP(NOW()), 1) <= 0
        ) 
    GROUP BY
        owner 
    ORDER BY
        NULL

CREATE TEMPORARY TABLE IF NOT EXISTS temp2 AS SELECT
        COUNT(rm_users.username) AS active_users,
        owner 
    FROM
        `rm_users` 
    LEFT JOIN
        `rm_services` 
            ON rm_users.srvid = rm_services.srvid 
    WHERE
        1 = 1 
        AND rm_users.enableuser = '1' 
        AND rm_users.acctype = '0' 
        AND (
            rm_services.limitdl = 0 
            OR if(rm_services.limitdl = 1, rm_users.downlimit - (SELECT
                COALESCE(SUM(radacct.acctoutputoctets),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) - (SELECT
                COALESCE(SUM(rm_radacct.dlbytes),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) > 0
        ) 
        AND (
            rm_services.limitul = 0 
            OR if(rm_services.limitul = 1, rm_radacct.uplimit - (SELECT
                COALESCE(SUM(radacct.acctinputoctets),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) - (SELECT
                COALESCE(SUM(ulbytes),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) > 0
        ) 
        AND (
            rm_services.limitcomb = 0 
            OR if(rm_services.limitcomb = 1, rm_radacct.comblimit - (SELECT
                COALESCE(SUM(radacct.acctinputoctets + radacct.acctoutputoctets),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) + (SELECT
                COALESCE(SUM(rm_radacct.ulbytes + rm_radacct.dlbytes),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) > 0
        ) 
        AND (
            rm_services.limituptime = 0 
            OR if(rm_services.limituptime = 1, rm_radacct.uptimelimit - (SELECT
                COALESCE(SUM(radacct.acctsessiontime),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) - (SELECT
                COALESCE(SUM(radacct.acctsessiontime),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) > 0
        ) 
        AND (
            rm_services.limitexpiration = 0 
            OR if(rm_radacct.limitexpiration = 1, UNIX_TIMESTAMP(rm_radacct.expiration) - UNIX_TIMESTAMP(NOW()), 1) > 0
        ) 
    GROUP BY
        owner 
    ORDER BY
        NULL

CREATE TEMPORARY TABLE IF NOT EXISTS temp3 AS SELECT
        COUNT(rm_users.username) AS disabled_users,
        owner 
    FROM
        `rm_users` 
    WHERE
        1 = 1 
        AND rm_users.acctype = '0' 
        AND rm_users.enableuser = '0' 
    GROUP BY
        owner 
    ORDER BY
        NULL

SELECT
        temp1.expired_users,
        temp2.active_users,
        (SELECT
            rm_phpsess.ip 
        FROM
            rm_phpsess 
        WHERE
            rm_managers.managername = rm_phpsess.managername 
        ORDER BY
            rm_phpsess.lastact DESC LIMIT 1) AS ipip,
        temp3.disabled_users,
        (SELECT
            (SUM(rm_services.unitprice) + SUM(rm_services.unitpricetax)) 
        FROM
            `rm_users` 
        LEFT JOIN
            `rm_services` 
                ON rm_users.srvid = rm_services.srvid 
        WHERE
            rm_users.owner = rm_managers.managername 
            AND rm_users.acctype = '0' 
            AND rm_users.expiration > '$date') AS sum_users,
        rm_managers.firstname,
        rm_managers.lastname,
        rm_managers.managername,
        rm_managers.balance,
        rm_managers.comment,
        rm_managers.enablemanager,
        rm_managers.country,
        rm_managers.city,
        rm_managers.state,
        rm_managers.email,
        rm_managers.zip,
        rm_managers.address,
        rm_managers.company,
        rm_managers.phone,
        rm_managers.mobile,
        rm_managergroups.mangroupid,
        rm_managergroups.mangroupname,
        t2.lastseen,
        t2.onlinecount,
        t2.lastip 
    FROM
        `rm_managers` 
    LEFT JOIN
        `rm_managergroups` 
            ON rm_managergroups.mangroupid = rm_managers.mangroupid 
    LEFT OUTER JOIN
        (
            SELECT
                rm_phpsess.managername,
                MAX(rm_phpsess.lastact) AS lastseen,
                (COUNT(if(rm_phpsess.closed = '0',
                1,
                NULL))) AS onlinecount,
                (rm_phpsess.ip) AS lastip 
            FROM
                `rm_phpsess` 
            GROUP BY
                rm_phpsess.managername 
            ORDER BY
                rm_phpsess.lastact DESC
        ) t2 
            ON t2.managername = rm_managers.managername 
    LEFT JOIN
        temp1 
            ON temp1.owner = rm_managers.managername 
    LEFT JOIN
        temp2 
            ON temp2.owner = rm_managers.managername 
    LEFT JOIN
        temp3 
            ON temp3.owner = rm_managers.managername
2 added 235 characters in body
source | link
  1. Replace the subqueries in the SELECT clause with temporary tables that you JOIN to, in the original query (scroll down for the actual temporary table creation and query transformations). The advantage of these temporary tables instead of subqueries is that each of these small temp table queries can be indexed so the search will be lightning fast, instead of the subqueries which can't be indexed properly in many cases.
  2. Add explicityexplicitly ORDER BY NULL in places where you have a GROUP BY clause but you do not care about ordering.
  3. Find all places you're comparing an integer column with a "string value" and avoid that, as it causes an unnecessary cast. For example, replace AND acctype = '0' with AND acctype = 0
  4. Try to avoid OR conditions if you can, as they are not indexable.
  5. Create the proper indexes for the query (see below).
  1. Replace the subqueries in the SELECT clause with temporary tables that you JOIN to, in the original query (scroll down for the actual temporary table creation and query transformations).
  2. Add explicity ORDER BY NULL in places where you have a GROUP BY clause but you do not care about ordering.
  3. Find all places you're comparing an integer column with a "string value" and avoid that, as it causes an unnecessary cast. For example, replace AND acctype = '0' with AND acctype = 0
  4. Try to avoid OR conditions if you can, as they are not indexable.
  5. Create the proper indexes for the query (see below).
  1. Replace the subqueries in the SELECT clause with temporary tables that you JOIN to in the original query (scroll down for the actual temporary table creation and query transformations). The advantage of these temporary tables instead of subqueries is that each of these small temp table queries can be indexed so the search will be lightning fast, instead of the subqueries which can't be indexed properly in many cases.
  2. Add explicitly ORDER BY NULL in places where you have a GROUP BY clause but you do not care about ordering.
  3. Find all places you're comparing an integer column with a "string value" and avoid that, as it causes an unnecessary cast. For example, replace AND acctype = '0' with AND acctype = 0
  4. Try to avoid OR conditions if you can, as they are not indexable.
  5. Create the proper indexes for the query (see below).
1
source | link

Several recommendations that can help here:

  1. Replace the subqueries in the SELECT clause with temporary tables that you JOIN to, in the original query (scroll down for the actual temporary table creation and query transformations).
  2. Add explicity ORDER BY NULL in places where you have a GROUP BY clause but you do not care about ordering.
  3. Find all places you're comparing an integer column with a "string value" and avoid that, as it causes an unnecessary cast. For example, replace AND acctype = '0' with AND acctype = 0
  4. Try to avoid OR conditions if you can, as they are not indexable.
  5. Create the proper indexes for the query (see below).

The temporary tables creation and the transformed query:

SELECT
        (SELECT
            COUNT(rm_users.username) 
        FROM
            `rm_users` 
        LEFT JOIN
            `rm_services` 
                ON rm_users.srvid = rm_services.srvid 
        WHERE
            rm_users.owner = rm_managers.managername 
            AND rm_users.acctype = '0' 
            AND rm_users.enableuser = '1' 
            AND (
                if(rm_services.limitdl = 1, rm_users.downlimit - (SELECT
                    COALESCE(SUM(acctoutputoctets),
                    0) 
                FROM
                    radacct 
                WHERE
                    radacct.username = rm_users.username) - (SELECT
                    COALESCE(SUM(dlbytes),
                    0) 
                FROM
                    rm_radacct 
                WHERE
                    rm_radacct.username = rm_users.username), 1) <= 0 
                OR if(rm_services.limitul = 1, rm_users.uplimit - (SELECT
                    COALESCE(SUM(acctinputoctets),
                    0) 
                FROM
                    radacct 
                WHERE
                    radacct.username = rm_users.username) - (SELECT
                    COALESCE(SUM(ulbytes),
                    0) 
                FROM
                    rm_radacct 
                WHERE
                    rm_radacct.username = rm_users.username), 1) <= 0 
                OR if(rm_services.limitcomb = 1, rm_users.comblimit - (SELECT
                    COALESCE(SUM(acctinputoctets + acctoutputoctets),
                    0) 
                FROM
                    radacct 
                WHERE
                    radacct.username = rm_users.username) + (SELECT
                    COALESCE(SUM(ulbytes + dlbytes),
                    0) 
                FROM
                    rm_radacct 
                WHERE
                    rm_radacct.username = rm_users.username), 1) <= 0 
                OR if(rm_services.limituptime = 1, rm_users.uptimelimit - (SELECT
                    COALESCE(SUM(acctsessiontime),
                    0) 
                FROM
                    radacct 
                WHERE
                    radacct.username = rm_users.username) + (SELECT
                    COALESCE(SUM(acctsessiontime),
                    0) 
                FROM
                    rm_radacct 
                WHERE
                    rm_radacct.username = rm_users.username), 1) <= 0 
                OR if(rm_services.limitexpiration = 1, UNIX_TIMESTAMP(rm_users.expiration) - UNIX_TIMESTAMP(NOW()), 1) <= 0
            )
        ) AS expired_users, (
            SELECT
                COUNT(rm_users.username) 
            FROM
                `rm_users` 
            LEFT JOIN
                `rm_services` 
                    ON rm_users.srvid = rm_services.srvid 
            WHERE
                owner = rm_managers.managername 
                AND enableuser = '1' 
                AND acctype = '0' 
                AND (
                    rm_services.limitdl = 0 
                    OR if(rm_services.limitdl = 1, downlimit - (SELECT
                        COALESCE(SUM(acctoutputoctets),
                        0) 
                    FROM
                        radacct 
                    WHERE
                        radacct.username = rm_users.username) - (SELECT
                        COALESCE(SUM(dlbytes),
                        0) 
                    FROM
                        rm_radacct 
                    WHERE
                        rm_radacct.username = rm_users.username), 1) > 0
                ) 
                AND (
                    rm_services.limitul = 0 
                    OR if(rm_services.limitul = 1, uplimit - (SELECT
                        COALESCE(SUM(acctinputoctets),
                        0) 
                    FROM
                        radacct 
                    WHERE
                        radacct.username = rm_users.username) - (SELECT
                        COALESCE(SUM(ulbytes),
                        0) 
                    FROM
                        rm_radacct 
                    WHERE
                        rm_radacct.username = rm_users.username), 1) > 0
                ) 
                AND (
                    rm_services.limitcomb = 0 
                    OR if(rm_services.limitcomb = 1, comblimit - (SELECT
                        COALESCE(SUM(acctinputoctets + acctoutputoctets),
                        0) 
                    FROM
                        radacct 
                    WHERE
                        radacct.username = rm_users.username) + (SELECT
                        COALESCE(SUM(ulbytes + dlbytes),
                        0) 
                    FROM
                        rm_radacct 
                    WHERE
                        rm_radacct.username = rm_users.username), 1) > 0
                ) 
                AND (
                    rm_services.limituptime = 0 
                    OR if(rm_services.limituptime = 1, uptimelimit - (SELECT
                        COALESCE(SUM(acctsessiontime),
                        0) 
                    FROM
                        radacct 
                    WHERE
                        radacct.username = rm_users.username) - (SELECT
                        COALESCE(SUM(acctsessiontime),
                        0) 
                    FROM
                        rm_radacct 
                    WHERE
                        rm_radacct.username = rm_users.username), 1) > 0
                ) 
                AND (
                    rm_services.limitexpiration = 0 
                    OR if(limitexpiration = 1, UNIX_TIMESTAMP(expiration) - UNIX_TIMESTAMP(NOW()), 1) > 0
                )
            ) AS active_users, (
                SELECT
                    ip 
                FROM
                    rm_phpsess 
                WHERE
                    rm_managers.managername = rm_phpsess.managername 
                ORDER BY
                    lastact DESC LIMIT 1
            ) AS ipip, (
                SELECT
                    COUNT(username) 
                FROM
                    `rm_users` 
                WHERE
                    owner = rm_managers.managername 
                    AND acctype = '0' 
                    AND enableuser = '0'
            ) AS disabled_users, (
                SELECT
                    (SUM(rm_services.unitprice) + SUM(rm_services.unitpricetax)) 
                FROM
                    `rm_users` 
                LEFT JOIN
                    `rm_services` 
                        ON rm_users.srvid = rm_services.srvid 
                WHERE
                    rm_users.owner = rm_managers.managername 
                    AND rm_users.acctype = '0' 
                    AND rm_users.expiration > '$date'
            ) AS sum_users, rm_managers.firstname, rm_managers.lastname, rm_managers.managername, rm_managers.balance, rm_managers.comment, rm_managers.enablemanager, rm_managers.country, rm_managers.city, rm_managers.state, rm_managers.email, rm_managers.zip, rm_managers.address, rm_managers.company, rm_managers.phone, rm_managers.mobile, rm_managergroups.mangroupid, rm_managergroups.mangroupname, t2.lastseen, t2.onlinecount, t2.lastip 
        FROM
            `rm_managers` 
        LEFT JOIN
            `rm_managergroups` 
                ON rm_managergroups.mangroupid = rm_managers.mangroupid 
        LEFT OUTER JOIN
            (
                SELECT
                    rm_phpsess.managername,
                    MAX(rm_phpsess.lastact) AS lastseen,
                    (COUNT(if(closed = '0',
                    1,
                    NULL))) AS onlinecount,
                    (ip) AS lastip 
                FROM
                    `rm_phpsess` 
                GROUP BY
                    rm_phpsess.managername 
                ORDER BY
                    lastact DESC
            ) t2 
                ON t2.managername = rm_managers.managername

The indexes:

ALTER TABLE
  `rm_managergroups`
ADD
  INDEX `rm_managergroups_index_1` (`mangroupid`, `mangroupname`);

ALTER TABLE
  `rm_managers`
ADD
  INDEX `rm_managers_index_1` (`mangroupid`, `managername`);

ALTER TABLE
  `rm_phpsess`
ADD
  INDEX `rm_phpsess_index_1` (`managername`, `lastact`, `ip`);

ALTER TABLE
  `rm_phpsess`
ADD
  INDEX `rm_phpsess_index_2` (`lastact`);

ALTER TABLE
  `rm_phpsess`
ADD
  INDEX `rm_phpsess_index_3` (`managername`, `lastact`, `closed`, `ip`);

ALTER TABLE
  `rm_services`
ADD
  INDEX `rm_services_index_1` (`srvid`, `unitprice`, `unitpricetax`);

ALTER TABLE
  `rm_users`
ADD
  INDEX `rm_users_index_1` (`acctype`, `srvid`, `owner`, `expiration`);

ALTER TABLE
  `temp1`
ADD
  INDEX `temp1_index_1` (`owner`, `expired_users`);

ALTER TABLE
  `temp2`
ADD
  INDEX `temp2_index_1` (`owner`, `active_users`);

ALTER TABLE
  `temp3`
ADD
  INDEX `temp3_index_1` (`owner`, `disabled_users`);

P.S, most recommendations were from this online sql query optimizer, and some were added manually.