>I need to gather a list of mailboxes and their current size, number >of messages waiting and the last date the user checked for new >email. I am working toward writing a program that will scan the >database, gather a list of messages that have been waiting for more >than X days and delete them. I only want to do this, if the user has >a large number of messages waiting AND they have not checked their >email since Y days ago.
getting the unread and undeleted messages in a mailbox, the mailboxname and username of the owner, his last login date and the date when the message was recived where the user has not logged in since 2003-08-12 16:25:24, ordered by the username SELECT messages.message_idnr,users.userid,mailboxes.name,users.last_login,messages.internal_date,messages.messagesize FROM messages, users, mailboxes WHERE mailboxes.owner_idnr=users.user_idnr AND messages.mailbox_idnr=mailboxes.mailbox_idnr AND users.last_login<'2003-08-12 16:25:24' AND messages.seen_flag=0 AND messages.deleted_flag=0 ORDER BY users.userid ASC with the result you could count up the messagesize ( size=size+result['messagesize'][i]) and decide whether to delete the message or not. You could extend the query to return only messages which are older then his logindate ( AND messages.seen_flag=0 AND messages.deleted_flag=0 AND messages.internal_date<users.last_login ORDER BY users.userid ASC ) so you can only delete mails, which he could have read the last time he was logged in... I hope I could help you... :D
