[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