[ic] Customer Housekeeping & IC 4.8.7 - Resend

[Mark Bryant]

At 13:10 02/03/2005, you wrote:
>Mark Bryant wrote:
>>Hi Everyone,
>>This is an edited resend as my last post seems to have slipped through 
>>the cracks.
>>I'm trying to use IC to do a bit of housekeeping via the Admin UI.....
>
>[snip]
>
>Hi Mark,
>
>Here is an SQL statement (I think it will work with your version of Mysql) 
>that will select all userdb records that are not found in the transaction 
>table.  ie, they have not placed orders.
>
>SELECT userdb.username from userdb LEFT JOIN transactions ON 
>userdb.username=transactions.username WHERE transactions.code IS NULL
>
>It would be risky to delete all records found by that statement since it 
>does not take into account the time since the creation of the account. So 
>if someone created an account 1 minute before that statement was run, it 
>would find that account, and delete it, if a delete statement was used.
>
>So probably filtering by mod_time in the userdb would be the way to find 
>all userdb records that have been created more than X time ago and have 
>not placed an order.
>
>I may be wrong, but I think that if a customer has created an account but 
>not placed an order, there will only be information in the userdb table 
>therefore, all you need to do is delete one row in the userdb table and 
>the customer is gone.

Hi Marty,

Thanks loads for answering my silly question.

Yes, that SELECT does seem to pull out the account names I need :)

All I need to figure out now is how to do it from a link on the 
customer.html admin page and I'm ready to go, although with the list I've 
got atm (it's not terribly large at 260 usernames) I could delete them 
manually and then try to stay on top of it on a monthly basis. A clickable 
link in the Admin UI would be more desirable long term of course.

I was aware of the possible implications you mentioned above about the age 
of the account, but due to my ignorance of what's being stored about a user 
account in terms of age (or how to work it out), I'd taken the view that 
I'd accept some recently created accounts will also be deleted during this 
process.

If it's easy enough to exclude accounts that are less than a month old then 
I'm interested in knowing how to do that as it's a better solution than the 
one I currently have above.

Once again, many thanks :)

Mark



Eros Shop
vwe internet ltd
PO BOX 1067
SLOUGH
SL1 7YA
UK

Shop - http://www.eros-shop.co.uk
EMail - info at eros-shop.co.uk
Tel - 0870 284 3369
Fax - 0870 284 4469