Refine parameters

[jrmg]

I have the following piece of code set up as a CGI, however I want to limit the 14 & 21 day clients it returns to only those who have a 'property_num' value of '4' - I want it to ignore all the others as they are no longer trial but full if this number is above 4.

Those at the "end of the one day trial" must get an email though - I would also like to be able to run the CGI more regularly (so new clients get their mail ASAP) whilst being sure not to send more than one mail per client!

Your thoughts?

<?php
/
Written 26/04/2002
Author PH

Checks for trial users whose trial period has run out.
** Sends them an email
/

require("../INC_common.php");

$trial_days = 14;
$xtension_days = 21;
$first_days = 0;

$xtension_file = "xtension_email.txt";
$end_file = "end_trial_email.txt";
$trial_file = "trial_email.txt";

$conn = mysql_connect(DB_HOST,DB_USER,DB_PASSWORD)
or die ("Could not connect to Database Server");
mysql_select_db(DB_NAME, $conn) or die ("Could not select the Database.");

/ Check for clients at end of 14 day trial /
$query = <<END_QUERY
select first_name, last_name, username, password, email
from clients
where (TO_DAYS(now())-TO_DAYS(date_created))='$trial_days'
END_QUERY;
$result = mysql_query($query)
or die(mysql_error());

$contents = file($xtension_file);

$headers = "From: Bob <[e-mail]>";
$subject = "Your bob trial has been extended";

while($row = mysql_fetch_array($result)){
$first = $row["first_name"];
$last = $row["last_name"];
$username = $row["username"];
$password = $row["password"];
$email = $row["email"];

/* Send email */
$temp_msg = $contents;
$msg = "";

foreach($temp_msg as $line){
$line = preg_replace("/FIRST\_NAME/", $first, $line);
$line = preg_replace("/LAST_NAME/", $last, $line);
$line = preg_replace("/USERNAME/", $username, $line);
$line = preg_replace("/PASSWORD/", $password, $line);

$msg .= $line;
}


$to = "$first $last <$email>";
	print "\n+ Sending mail to [$to]\n+ Subject [$subject]\n+ Message [$msg]\n+Headers [$headers]";
mail($to, $subject, $msg, $headers);

}

/ Check for clients at end of 1 day of trial /
$query = <<<END_QUERY
select first_name, last_name, username, password, email
from clients
where (TO_DAYS(now())-TO_DAYS(date_created))='$first_days'
END_QUERY;
$result = mysql_query($query)
or die(mysql_error());

$contents = file($trial_file);

$headers = "From: Bob <[e-mail]>";
$subject = "Your bob trial has been extended";

while($row = mysql_fetch_array($result)){
$first = $row["first_name"];
$last = $row["last_name"];
$username = $row["username"];
$password = $row["password"];
$email = $row["email"];

/* Send email */
$temp_msg = $contents;
$msg = "";

foreach($temp_msg as $line){
$line = preg_replace("/FIRST\_NAME/", $first, $line);
$line = preg_replace("/LAST_NAME/", $last, $line);
$line = preg_replace("/USERNAME/", $username, $line);
$line = preg_replace("/PASSWORD/", $password, $line);

$msg .= $line;
}


$to = "$first $last <$email>";
	print "\n+ Sending mail to [$to]\n+ Subject [$subject]\n+ Message [$msg]\n+Headers [$headers]";
mail($to, $subject, $msg, $headers);

}

/ Check for clients at end of 21 day trial /
$query = <<<END_QUERY
select client_id, first_name, last_name, username, password, title, dob_day,
dob_month, dob_year, type_address, street_pobox, street, city_suburb,
company, zip_post_code, branch_hq, state_county, additional_addr1,
additional_addr2, country, email, phone, fax, mobile, date_created,
last_login_date
from clients
where (TO_DAYS(now())-TO_DAYS(date_created))='$xtension_days'
END_QUERY;
$result = mysql_query($query)
or die(mysql_error());

$count = mysql_num_fields($result);

$contents = file($end_file);

$headers = "From: Bob <[e-mail]>";
$subject = "Your bob trial has been extended";

while($row = mysql_fetch_array($result)){
$first = $row["first_name"];
$last = $row["last_name"];
$email = $row["email"];
$username = $row["username"];
$client_id = $row["client_id"];

print("have found $first $last");
");/ Send email /
$temp_msg = $contents;
$msg = "";

foreach($temp_msg as $line){
$line = preg_replace("/FIRST_NAME/", $first, $line);
$line = preg_replace("/LAST_NAME/", $last, $line);

$msg .= $line;
}

$to = "$first $last <$email>";
mail($to, $subject, $msg, $headers);

/* Change database - Move client to dead clients*/

for($i=0; $i<$count; $i++){
$row[$i] = addslashes($row[$i]);
}

$query = <<<END_QUERY
insert into dead_clients
(client_id, first_name, last_name, username, password, title, dob_day,
dob_month, dob_year, type_address, street_po, street, city_suburb, company,
zip_post_code, branch_hq, state_county, additional_address1,
additional_address2, country, email, phone, fax, mobile, date_created,
last_login_date)
values ('$row[0]', ','$row[1]', ','$row[2]',
','$row[3]', ','$row[4]', ','$row[5]', ','$row[6]',
','$row[7]', ','$row[8]', ','$row[9]', ','$row[10]',
','$row[11]', ','$row[12]', ','$row[13]', ','$row[14]',
','$row[15]', ','$row[16]', ','$row[17]',
','$row[18]', ','$row[19]', ','$row[20]', ','$row[21]',
','$row[22]', ','$row[23]', ','$row[24]', ','$row[25]')
END_QUERY;

mysql_query($query)
or die(mysql_error());

/* Delete DB entries */

$query = <<<END_QUERY
delete from client_users where client_id='$client_id'
END_QUERY;

mysql_query($query)
or die(mysql_error());

$query = <<<END_QUERY
delete from client_user_property_type where client_id='$client_id'
END_QUERY;

mysql_query($query)
or die(mysql_error());

$query = <<<END_QUERY
delete from properties where client_id='$client_id'
END_QUERY;

mysql_query($query)
or die(mysql_error());

$query = <<<END_QUERY
delete from clients where client_id='$client_id'
END_QUERY;

mysql_query($query)
or die(mysql_error());

/* Delete files */
$from_path = "../clients/$username/";
if ($handle = opendir($from_path)) {

while (false !== ($file = readdir($handle))) { 
if($file == '.' || $file == '..') { continue; }	//ignore . and ..

$path = "$from_path" . "$file";

if(is_file($path)){
    unlink($path) or die("Could not delete $path");
}

if(is_dir($path)){
   $path .= "/";
   if($inner_dir = opendir($path)){
	while(false !== ($inner_file = readdir($inner_dir))){
	    $inner_path = "$path" . "$inner_file";
	    if($inner_file == '.' || $inner_file == '..') { continue; }	//ignore . and ..
	    unlink($inner_path) or die("Could not delete $inner_path");
	}
    }
    closedir($inner_dir);
    rmdir($path) or die("Could not remove $path");

}



}

}

closedir($handle); 
rmdir($from_path) or die("Could not remove $path");

}");

[sarahk]

I think you need to break your posting into smaller chunks, noone here is going to write your app for free, but we're happy to help.

First up, if you want to send an email when the trial expires you need to either be very disciplined and login to your site daily and call a script to do that or set up a cron job if your host will let you.

If you have a high volume site you can hijack other functions to do this by having them also run the email script. This may result in the code running 2 or more times at the same time so you need to be sure that you have a method of pseudo record locking sorted out. Maybe record a session idea instead of the simple flag and only send the email if you've been able to update with the sessionid.

You need to log if an email has been sent, which is as simple as saying where emailsent = 0 and when you send each one update the flag to 1.

Good luck

[jrmg]

OK,

Thanks for your help. I am in the process of setting up a cron, so that's sorted.

Really all I need to know is about the 'property_num' value of '4'.

As you mention, it works fine at present if I simply visit the file via IE, but I have tried various options to refine it, such as:

where (TO_DAYS(now())-TO_DAYS(date_created))='$trial_days'
AND property_num < 5";

But as soon as I do this, it doesn't work at all? I thought maybe there was a problem with the whole logic of the code or I'm missing something key elsewhere when making this alteration.

What are your thoughts/suggestions - JR.