[ic] Customer Housekeeping & IC 4.8.7 - Resend

[Sam Batschelet]

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

West Branch Resort 
www.westbranchresort.com