View Single Post
  #7  
Old 12th June 2013, 00:17
cbj4074 cbj4074 is offline
Senior Member
 
Join Date: Nov 2010
Posts: 368
Thanks: 25
Thanked 47 Times in 42 Posts
Default

Quote:
YEs, he would have to reallocate the space aat it might also be that he wants to increase the psca only for some mailboxes or that he wishes to add additional mailboxes which require the space.
That's an excellent point, and that's where the topic of quotas becomes tricky. I'll attempt to brainstorm different ways that this challenge might be approached.

In the meantime, I'm going to document some of the queries that may be useful and relevant once I (or anyone else) gets around to building a Tools module to handle quotas in more advanced ways.

"Get all clients whose combined mailbox quotas exceed the client mail quota." This query might be useful in determining which clients' quotas need to be "reigned-in". This is the situation I am facing myself, and it doesn't seem like an uncommon scenario (basic use-case is when an Admin creates a client's mailboxes with unlimited quotas and then a client quota is introduced later).
Code:
SELECT
    `mail_user`.`sys_groupid` AS `groupId`,
    `client`.`client_id` AS `clientId`,
    `client`.`limit_mailquota`,
    FORMAT(`client`.`limit_mailquota`, 2) AS `mailQuota`,
    FORMAT(SUM(`quota`)/1024/1024, 2) AS `groupQuota`
FROM `mail_user`
INNER JOIN `sys_group` ON `mail_user`.`sys_groupid` = `sys_group`.`groupid`
INNER JOIN `client` ON `sys_group`.`client_id` = `client`.`client_id`
GROUP BY `client`.`client_id`
HAVING
    `limit_mailquota` > -1
    AND
    `groupQuota` > 0
    AND
    SUM(`quota`)/1024/1024 > (SELECT `limit_mailquota` FROM `client` WHERE `client_id` = `clientId`)
"Update all mailbox quotas for client with ID %d such that the available client quota is divided evenly across all client mailboxes."
Code:
UPDATE `mail_user`
INNER JOIN `sys_group` ON `mail_user`.`sys_groupid` = `sys_group`.`groupid`
SET `quota` = FLOOR((
    SELECT
        `updatedUserQuota`
    FROM (
        SELECT
            `mail_user`.`email`,
            (SELECT COUNT(*) FROM `mail_user` WHERE `sys_groupid` = `groupid`) AS `numClientMailboxes`,
            (`limit_mailquota`/ (SELECT COUNT(*) FROM `mail_user` WHERE `sys_groupid` = `groupid`)) AS `updatedUserQuota`
        FROM `client`
        INNER JOIN `sys_group` ON `client`.`client_id` = `sys_group`.`client_id`
        INNER JOIN `mail_user` ON `sys_group`.`groupid` = `mail_user`.`sys_groupid`
        WHERE
            `limit_mailquota` > -1
            AND `mail_user`.`sys_groupid` = %d
        GROUP BY `numClientMailboxes`
        HAVING
            `numClientMailboxes` > 0
    ) AS `derived`
)*1024*1024)
WHERE
    `mail_user`.`sys_groupid` = %d
I'm sure that I am missing a couple of crucial pieces, but at least this is a start, and for my own records, if no one else's.

Happy to receive any feedback, as this is my first attempt at working with ISPC's DB tables in a development capacity.

Last edited by cbj4074; 12th June 2013 at 21:55.
Reply With Quote