Gossamer Forum
Home : Products : DBMan : Customization :

Stand Alone Email Script: Sending mail based on two fields in database

Quote Reply
Stand Alone Email Script: Sending mail based on two fields in database
Carol,

Hi there...Again, glad that you back with us.
Smile

I decided to start a new thread since the older Thread was getting quite long.
This Thread is a continuation of the following Thread:

http://www.gossamer-threads.com/scripts/forum/resources/Forum12/HTML/000628.html

Okay, I think I have identified the problem with this mod. I have added a print statement to see what is being pulled from the database and I notice that email addresses seem to be printing all on one line. What I think I need to do is have the email addresses print on separate lines before sending the email message.

Here is what I have (to re-cap):

Code:
#############################################################
# Required Files
#############################################################

require $mailer_path . "/Mailer.pm";

##############################################################
# Begin Script
#############################################################

@referers = ('http://www.coco.cc.az.us','http://wookie.coco.cc.az.us','http://spock.coco.cc.az.us');

&Check_URL;

#############################################################
# Open the Database
#############################################################

open (DB, "<$db_path/$db_file_name") or ("unable to open password file. Reason: $!\n");
if ($db_use_flock) {
flock(DB, 2);
}
@lines = <DB>;


#############################################################
# Start Loop
#############################################################

foreach $line (@lines) {
@data = split /\|/, $line;
$email = $data[21];
$user = $data[3];
print "$email\n";
}
if (!$data[21]) {
print "Content-type: text/html\n\n";
print "No Email Address or User Name defined in $db_path/$db_file_name!";
exit;
}
close DB;

if (($user) && ($email)) {

#############################################################
# Mail Routine
#############################################################

my $mailer = new Mailer ( { smtp => 'yoda.coco.cc.az.us' } )
or die "Can't init mailer: $Mailer::error";
$mailer->send ( {
to => '$email',
from => '$admin_mail',
subject => "NT Web Account Created",
msg => $notify_body_text
} )
or die "Can't send mail: $Mailer::error";
}

#############################################################
# Print to Log File
#############################################################

&logging;

##################################################################
# END SCRIPT
##################################################################

The output of this file when I execute it from the web browser is the following:

Code:
lhill@anthrotech.com aolson@anthrotech.com apeterse@anthrotech.com brybates@anthrotech.com cnation@flagstaff.az.us
dlokon@anthrotech.com
lgallego@anthrotech.com minigo@anthrotech.com mmckinst@anthrotech.com shill@anthrotech.com vfishco@anthrotech.com
twymore@anthrotech.com tmckever@anthrotech.com snittman@anthrotech.com rbradfor@anthrotech.com
jrhode@anthrotech.com brunke@anthrotech.com cshirley@anthrotech.com dmacias@anthrotech.com ejones@anthrotech.com jgroos@anthrotech.com mostrows@anthrotech.com eliot@anthrotech.com
dbrown@anthrotech.com psprengl@anthrotech.com mlainoff@anthrotech.com bswanson@besy.anthrotech.com ksalt@anthrotech.com
mbaker@anthrotech.com kkozak@anthrotech.com bcress@anthrotech.com bmurphy@anthrotech.com dfischo@anthrotech.com gcurd@anthrotech.com jbradley@anthrotech.com rcalvin@anthrotech.com
rfoster@anthrotech.com
pholbroo@anthrotech.com Content-type: text/plain

I think what I need for this to work with our SMTP server is to have the email addresses on separate lines, like the following:

Code:
email1
email2
email3
email4
email5

Any additional thoughts you have for making this work would be greatly appreciated. Also, there have been about three or four other DBMAN users who would like to have this type of script for their database projects.

Smile

Thanks in advance.

Regards,

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us

[This message has been edited by Eliot (edited September 23, 1999).]

[This message has been edited by Eliot (edited September 23, 1999).]
Quote Reply
Re: Stand Alone Email Script: Sending mail based on two fields in database In reply to
Eliot - sounds good - I'll take a look once you have it working.

Thanks!
-----------------
donm
Quote Reply
Re: Stand Alone Email Script: Sending mail based on two fields in database In reply to
Carol et al,

I was wondering what you thought about adding in the codes from the Auto Deletion mod into this script. My boss has requested that password changes remain on a staggered schedule. I mean the easiest thing for me to do would be to automate this email script to send out messages once a month to all users with the specified account and who have email addresses. I do have a LastModified Field.

Do you think the script would still work if I added the following codes surrounding the new mail routine codes you, Carol, have provided?

Code:
foreach (@lines) {
next if /^#/;
next if /^\s*$/;
chomp;
@values = &split_decode ($_);
if ($today > (&date_to_unix($values[$dateadded_field]) + (86400 * $values[$removeby_field]))) {
print "Record(s) Deleted\n";
next;
}

Of course, I would have to add $today variables, date sub-routines, change the dateadded field with LastModified, and also modify the today values to make sure that it checks for today and LastModified date.

Am I missing something? Or can the codes in the Auto Deletion script be applied to this script to allow sending email messages in a staggered rotation.

Let me know.

Thanks.

Regards,

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us
Quote Reply
Re: Stand Alone Email Script: Sending mail based on two fields in database In reply to
It might work, Eliot. It's been a long time since I looked at the autodelete mod, and I didn't write it in the first place, so I don't recall all the ins and outs of it. You can give it a try, though and see what happens.


------------------
JPD





Quote Reply
Re: Stand Alone Email Script: Sending mail based on two fields in database In reply to
Thanks for putting the URL to the old discussion in. It helped a lot! Smile

A couple of things I see right off.

I would close the DB file immediately after

@lines = <DB>;

You have all the data at that point, so there's no need to keep it open. (I don't know if it would make any difference or not. It may be that I'm a compulsive file-closer. Smile )

In this section:

Code:
foreach $line (@lines) {
@data = split /\|/, $line;
$email = $data[21];
$user = $data[3];
print "$email\n";
}
if (!$data[21]) {
print "Content-type: text/html\n\n";
print "No Email Address or User Name defined in $db_path/$db_file_name!";
exit;
}

What will happen is that, if the last record doesn't have an email address, your script will exit, even if every other record has one.

What I would suggest is that you schlep all of the usernames and email addresses into arrays.

Code:
foreach $line (@lines) {
chomp $line;
@data = split /\|/, $line;
if ($data[21] && $data[3]) {
push (@emails,$data[21]);
push (@users,$data[3]);
}
}
if (!$emails[0]) {
print "Content-type: text/html\n\n";
print "No Email Address or User Name defined in $db_path/$db_file_name!";
exit;
}

# if you want to print out the email addresses to the browser, add the following:
else {
print "Content-type: text/html\n\n";
foreach $email (@emails) {
print "$email<BR>";
}
}

Are you actually using the $user variable in your mailer routine? If not, you can delete the line

push (@users,$data[3]);

If you are going to use it in your mailer, there will need to be a little difference in the code that follows.

To send email out to all of the users, use

Code:
foreach $email (@emails) {
my $mailer = new Mailer ( { smtp => 'yoda.coco.cc.az.us' } )
or die "Can't init mailer: $Mailer::error";
$mailer->send ( {
to => '$email',
from => '$admin_mail',
subject => "NT Web Account Created",
msg => $notify_body_text
} )
or die "Can't send mail: $Mailer::error";
}





------------------
JPD





Quote Reply
Re: Stand Alone Email Script: Sending mail based on two fields in database In reply to
Thanks, Carol. I appreciate the response. Yes, I am using the "user" field to compare whether or not they are certain type of user.
That is why I have the following line of code in my script:

Code:
if (($user) && ($email)) {

I do not want to send mass mail out to the whole database, just users who are have a certain type of access to network resources.

Hope this makes sense.

EDIT: Well, the codes you gave me did create a list of email addresses based on their user type....Nice. Smile However, no mail is sent.

Hmm...the SMTP routine I have in this script is exactly the same routine I have working in other DBMAN based projects and mail is sent out just fine with the other projects.

Thanks again for your help, Carol. I really appreciate it. If you have any other ideas, let me know.

Regards,

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us

[This message has been edited by Eliot (edited September 23, 1999).]
Quote Reply
Re: Stand Alone Email Script: Sending mail based on two fields in database In reply to
But you are not using the value of the the userid in the email, right?

Whether they have a userid or not is taken care of in the line

if ($data[21] && $data[3]) {

The email address is only added to the array if there is something in the userid field.

The problem with your original line of

if (($user) && ($email)) {

is that it will only send an email to the person with the last record.

If you don't understand why, I can give you an example that will explain it.


------------------
JPD





Quote Reply
Re: Stand Alone Email Script: Sending mail based on two fields in database In reply to
hi eliot...just happened across your post. i really doubt if this will help you at all seeing as how you are doing this in a mail script rather than a form, but it may be useful to you anyway. i found that when sending form info to an email address that i could get each bit of info to appear on a seperate line rather than all on the same line by putting the following attribute in to the <FORM> tag:

enctype="text/plain"

again....i doubt if this will help with your current problem, but i thought i would pass it along anyway.

chuck

------------------
pres@cantonconnection.com
Quote Reply
Re: Stand Alone Email Script: Sending mail based on two fields in database In reply to
Thanks again, Carol... Smile

cvsiv,

Thanks for the suggestion. The codes Carol gave did enable the list to be created with email addresses on separate lines. However, the problem now is that it will not send the mail out.

Also, I am not pulling these email addresses via any Web Forms, just from the DBMAN database. I am checking two values (user type and email address from the fields in the database) and then sending out mass mail to people who have both of these fields.

Hmm...

Regards,

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us

[This message has been edited by Eliot (edited September 23, 1999).]
Quote Reply
Re: Stand Alone Email Script: Sending mail based on two fields in database In reply to
So you have the email addresses in an array, right?

I saw there was a mention of this in the previous thread, but I'm still wondering about the

Code:
to => '$email',
from => '$admin_mail',

Perl does not interpret variables if the are within single quotes.

Try taking out the quotes:

Code:
to => $email,
from => $admin_mail,



------------------
JPD





Quote Reply
Re: Stand Alone Email Script: Sending mail based on two fields in database In reply to
Carol,

Welp, that worked! Smile But very strange that I have the single quotes surrounding the email addresses in my other DBMAN scripts, and they work just fine!

Great. Now, I am going to test this script with the AT Scheduler program. I am sure it will work fine.

Smile

donm,

If you are reading this Thread, I will post the codes in a text file and submit it in the Resource Center as soon as I clean up the codes and also add a Sendmail function in the program to accomodate those users who use sendmail rather than SMTP. I think that this script with a little tweaking will accomodate your needs with regards to sending out mass mail messages to your DBMAN users.

Thanks!

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us
Quote Reply
Re: Stand Alone Email Script: Sending mail based on two fields in database In reply to
Yep, it worked!

I added the following variables: (in preparation to make the script user friendly for other users.)

Code:
$mailfield = 21;
$userfield = 3;
$modifiedfield = 19;
$reminddays = '42';

Then I simply added two sub-routines:

sub get_date
sub &date_to_unix

Then I added the following if statement above the other if statement:

Code:
if ($today > (&date_to_unix($data[$modifiedfield]) + (86400 * $reminddays))) {
OTHER IF AND PUSH CODES
}

The script created a list of users that are only an account type from records modified past 42 days.

Cool!

I will test this thoroughly on my UNIX server at home and also clean up the codes a bit, and release it first in this Forum for others to test, then release it as a formal mod (with your permission, Carol) in the Resource Center.

Smile

Regards,

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us
Quote Reply
Re: Stand Alone Email Script: Sending mail based on two fields in database In reply to
No need to ask my permission, Eliot.

I do ask that anyone who just copies one of my mods from my site give me credit, but this was developed in the forum, so as far as I'm concerned, it's yours.


------------------
JPD





Quote Reply
Re: Stand Alone Email Script: Sending mail based on two fields in database In reply to
Thanks, Carol! Smile

I hope this Mod helps other DBMAN users with their email needs.

Regards,

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us
Quote Reply
Re: Stand Alone Email Script: Sending mail based on two fields in database In reply to
Eliot - were you able to get this to work?

------------
donm
Quote Reply
Re: Stand Alone Email Script: Sending mail based on two fields in database In reply to
donm,

Yes...it works (on both UNIX/NT and Sendmail/SMTP). I am in the process of cleaning up the script and adding in more user-friendly variables. Also, I am working on documentation that will assist users in setting up this script.

I should have it completed in the next few weeks (I am in the midst of writing papers for conferences and also preparing a technical presentation for a job interview next week). I will submit it for Alex's review to be placed in the Resource Center at that time.

Regards,

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us
Quote Reply
Re: Stand Alone Email Script: Sending mail based on two fields in database In reply to
Thanks !
--------
donm