[ic] Customer Housekeeping & IC 4.8.7 - Resend

Eros Shop info at eros-shop.co.uk
Wed Mar 16 11:32:59 EST 2005


At 01:58 14/03/2005, you wrote:



>On 3/12/05 7:55 AM, "Mark Bryant" <mark at vwe.net> wrote:
>
> > At 23:31 04/03/2005, you wrote: On 03/04/05 07:22, Mark Bryant wrote: I
> > suspect the solution is to upgrade to a newer version of MySQL as 
> clearly 3.23
> > is lacking in a fair bit of functionality.
> >
> >> Try this:
> >>
> >> SELECT userdb.username from userdb LEFT JOIN transactions ON
> >> userdb.username=transactions.username WHERE transactions.code IS NULL AND
> >> mod_time < (UNIX_TIMESTAMP() - 2592000)
> >>
> >> Note that 2592000 comes from 60*60*24*30 (seconds in a minute, minutes 
> in an
> >> hour, hours in a day, days in a month).
> >>
> >> Barring that you can always do up a short Perl script using the DBI 
> module to
> >> replace the missing functionality of your version of mysql.
> >>
> >> Peter
> >>
> > I'd like to say thank you to everyone that helped me with this.
> >
> > I've finally found a solution by adding a new admin UI page called
> > customer_expire.html and adding the following row to the icmenu table 
> so that
> > it can be accessed under the customer area.
> >
> > 00003   Customers 
> 11                                    admin/customer_expire
> > Expire Customers
> >
> > The code for the new admin page is as follows.....
> >
> > [set page_title][L]Customer manager[/L]: [L]Expire customers[/L][/set] [set
> > ui_class]Customers[/set] [set page_banner][L]Expire Cusomters[/L][/set] 
> [set
> > icon_name]icon_people.gif[/set] [set ui_class]Customers[/set] [set
> > page_perm]userdb[/set] [flag type=write table=userdb] @_UI_STD_HEAD_@ <!--
> > ----- BEGIN REAL STUFF ----- --> <table border=0> <tr> <td
> > bgcolor=__UI_C_TOPBLOCKBAR__><img src="bg.gif" height=1></td> </tr> 
> <tr> <td
> > bgcolor="__UI_C_INTBLOCK__" valign=top>
> >
> > [perl tables="userdb transactions"] my $db = $Db{userdb}; return "No userdb
> > table???!" if ! $db; my $query = "SELECT userdb.username from userdb 
> LEFT JOIN
> > transactions ON userdb.username=transactions.username WHERE 
> transactions.code
> > IS NULL AND FROM_UNIXTIME(mod_time) < SUBDATE(NOW(), INTERVAL 1 MONTH)"; my
> > $ary = $db->query( { sql => $query, ml => 1 } ); for(@$ary) { my ($un) 
> = @$_;
> > $out .= "$un "; #               if ($db->delete_record($un) ) { $out .=
> > "Deleted! "} $out .= "<BR>"; } return $out; [/perl] </td> </tr> <tr> <td
> > bgcolor=__UI_C_TOPBLOCKBAR__><img src="bg.gif" width=__UI_MAIN_WIDTH__
> > height=1></td> </tr> </table> </form> <!-- ----- END REAL STUFF ----- -->
> > @_UI_STD_FOOTER_@ <!-- page: @@MV_PAGE@@ -->
> >
> >
> > I realise with the comment still in place on the delete line that this code
> > does nothing but display a list of usernames, but before I go ahead and 
> remove
> > it, can anyone see any silly mistakes?
> >
> > Also, do I need the last line of this page <!-- page: @@MV_PAGE@@ --> as it
> > was present on the UI page I copied and chopped about to make the 
> above? It's
> > commented out so I assume it's not being used by the browser for anything
> > useful.
> >
> > Many thanks
> >
> > Mark
> >
>Just a couple comments:  The best way to test this would be on a development
>Catalog vs live especially using the delete command.  I am sure that is what
>your doing but I have learned the hard way and having your catalog down a
>few mintues while you realize what you did and then reinstall the database
>that you backed up before you tried right...?  Looks cool though nice work!
>
>-Sam

Hi Sam,

Thanks for the words of confidence and support, and yes I'm testing this on 
a dummy/dev catalog :)

I've had to amend the final select I'm using:

SELECT userdb.username from userdb LEFT JOIN transactions ON 
userdb.username=transactions.username WHERE transactions.code IS NULL AND 
FROM_UNIXTIME(mod_time) < SUBDATE(NOW(), INTERVAL 1 MONTH) AND userdb.fname 
IS NOT NULL ORDER BY username

The one thing that I never picked up on, is that when an account is created 
as just a user and pass pair, it's given a unix time of 00000000000000 and 
not the current time as one would assume it should. Why that is I don't 
know, but nevertheless it's a fact.

I therefore exclude these zero dated accounts by looking to see if their 
first name is present. If the account was saved then we'd have a mod_time 
that's sensible and a name as the form will insist they supply one, in the 
absence of a name it's likely it's an incomplete account.

My install of IC is using the mysql defaults in dbconfig/mysql/userdb.mysql

Database  userdb  COLUMN_DEF   "mod_time=timestamp"

It appears from monitoring how the DB changes that mod_time is ONLY set 
when someone either saves account information or checks out. If mod_time 
can be set at during checkout and account updates, I'm assuming it can also 
be done when the account is initially created. Is there a quick and easy 
way to achieve this as I think my select based solution is a work around 
more than anything else?

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




More information about the interchange-users mailing list