[ic] Customer Housekeeping & IC 4.8.7 - Resend
Mark Bryant
mark at vwe.net
Sat Mar 12 07:55:33 EST 2005
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
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