URGENT HELP REQUESTED! I need to write a script to use MySql database.

Hello experts!

I am in a hole right now and I need some quick answers, if possible. Thank you in advance!

I need to do two things:
1. I need to ensure that the code I am using is bug-free and works.
2. I need to e-mail (preferably using SendMail) customers using values from the database.

Please see the code below:

#!/usr/bin/perl -w

#-----------------------------------------
# $Id$
#
# This script will generate the monthly
# bills to customers to pay through PayPal for website
#
#-----------------------------------------

use strict;
use Date::Format;
use DBI;

my $amount_owed = "";
my $billing = "";

# Open Log File.
open(LF, "+>", "./billing.log") || die "Failed to open Logfile: $!\n";

# Connect to the database
my $c = &Database;

# Function 'time2str' is included
# in 'Date::Format'.
my $today = time2str("%Y-%m-%d", time);
print LF "$today - Running\n";

# The first routine we will run will
# grab all of the pertinent information
# from the 'billing_history' table.
my $query = "SELECT b.*
            FROM billing_history b
           WHERE b.due_date LIKE '$today %'
           AND b.payment_amount='0.00'";

my $sth = $c->prepare($query);
$sth->execute();

print LF "$today - $query\n";

my ($id,
    $store_id,
    $user_type,
    $billing_amount,
    $due_date,
    $billing_date,
    $last_payment_made,
    $payment_date,
    $days_past_due,
    $payment_amount);

$sth->bind_columns(\($id,
                 $store_id,
                 $user_type,
                 $billing_amount,
                 $due_date,
                 $billing_date,
                 $last_payment_made,
                 $payment_date,
                 $days_past_due,
                 $payment_amount));

while ($sth->fetch) {

      $amount_owed = sprintf("%01.2f", $billing_amount);

      print LF "$today - $store_id : $user_type : amount owed = $amount_owed\n";            

      # Now we have the amount that this store_id owes,
      # if it is greater than zero (0), we will send them an e-mail to pay through PayPal, otherwise we will just update their
      # 'due_date' for another month.
      if ($amount_owed > 1.12) {

            print LF "$today - $store_id : will be charged.\n";

            # If this user owes any money, then we will have
            # to grab their credit card number from the DB.

            my $query = "      SELECT configuration_value AS email
                                    FROM configuration
                                    WHERE configuration.store_id='$store_id' AND configuration_key='OWNER_EMAIL'";
            
            my $ref = $c->prepare($query);
            $ref->execute();
            my ($email);
            $ref->bind_columns(\($email));
            $ref->fetch;
            my $cur_email = $email;

            print LF "$today - $store_id : activation reference = @$ref[0]\n";

            open(MAIL, "|/usr/sbin/sendmail -t");

            print MAIL "Content-type: text/html\n";
            print MAIL "FROM: accounts\@offersplus.com\n";
            print MAIL "TO: ".$cur_email;
            print MAIL "SUBJECT: Monthly Service Charge\n\n";
            my $billing = "<HTML><HEAD></HEAD>
            print MAIL $billing;

            close(MAIL);
      }
}

# Subroutines
sub Database {

        # Connection info.
        my $db_type = "mysql";
        my $db_host = "hostname";
            #my $db_host = "localhost";
        my $db_sock = "/var/lib/mysql/mysql.sock";
        my $db_user = "username";
        my $db_pass = "password";
        my $db_dbse = "dbname";

        my $c = DBI->connect("DBI:$db_type:$db_dbse:$db_host",
                      "$db_user",
                      "$db_pass",
                      { RaiseError => 1 } )
       || die "Cannot connect to DB server: $DBI::errstr .\n";

        return $c;
}
LVL 1
TZRickAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kanduraCommented:
What's your hole, exactly?

> 1. I need to ensure that the code I am using is bug-free and works.

You should write unit tests. Make sure each part works as you want it.

the code looks like it emails your customers based on certain criteria from the database.
0
TZRickAuthor Commented:
Hi Kandura!

Thank you for your response! The thing is, I am a Perl newbie (with little time to learn right now), and I need to modify the script to be able to send e-mails properly. If I run the script as is, I get:

Bareword found where operator expected at the_biller.pl line 110, near "my $db_type = "mysql"
  (Might be a runaway multi-line "" string starting on line 99)
        (Do you need to predeclare my?)
String found where operator expected at the_biller.pl line 111, near "my $db_host = ""
  (Might be a runaway multi-line "" string starting on line 110)
        (Missing semicolon on previous line?)
Bareword found where operator expected at the_biller.pl line 111, near "my $db_host = "db"
        (Do you need to predeclare my?)
String found where operator expected at the_biller.pl line 112, near "#my $db_host = ""
  (Might be a runaway multi-line "" string starting on line 111)
        (Missing semicolon on previous line?)
Bareword found where operator expected at the_biller.pl line 112, near "#my $db_host = "localhost"
        (Missing operator before localhost?)
String found where operator expected at the_biller.pl line 113, near "my $db_sock = ""
  (Might be a runaway multi-line "" string starting on line 112)
        (Missing semicolon on previous line?)
String found where operator expected at the_biller.pl line 114, near "my $db_user = ""
  (Might be a runaway multi-line "" string starting on line 113)
        (Missing semicolon on previous line?)
Bareword found where operator expected at the_biller.pl line 114, near "my $db_user = "offersplus"
        (Do you need to predeclare my?)
String found where operator expected at the_biller.pl line 115, near "my $db_pass = ""
  (Might be a runaway multi-line "" string starting on line 114)
        (Missing semicolon on previous line?)
Bareword found where operator expected at the_biller.pl line 115, near "my $db_pass = "op2005"
        (Do you need to predeclare my?)
String found where operator expected at the_biller.pl line 116, near "my $db_dbse = ""
  (Might be a runaway multi-line "" string starting on line 115)
        (Missing semicolon on previous line?)
Bareword found where operator expected at the_biller.pl line 116, near "my $db_dbse = "offersplus"
        (Do you need to predeclare my?)
String found where operator expected at the_biller.pl line 118, near "my $c = DBI->connect(""
  (Might be a runaway multi-line "" string starting on line 116)
        (Missing semicolon on previous line?)
Bareword found where operator expected at the_biller.pl line 118, near "my $c = DBI->connect("DBI"
        (Do you need to predeclare my?)
String found where operator expected at the_biller.pl line 119, near """
  (Might be a runaway multi-line "" string starting on line 118)
Global symbol "$db_type" requires explicit package name at the_biller.pl line 99.
syntax error at the_biller.pl line 110, near "my $db_type = "mysql"
Global symbol "$db_host" requires explicit package name at the_biller.pl line 110.
Global symbol "$db_host" requires explicit package name at the_biller.pl line 111.
Global symbol "$db_sock" requires explicit package name at the_biller.pl line 112.
Global symbol "$db_user" requires explicit package name at the_biller.pl line 113.
Global symbol "$db_pass" requires explicit package name at the_biller.pl line 114.
Global symbol "$db_dbse" requires explicit package name at the_biller.pl line 115.
Global symbol "$db_type" requires explicit package name at the_biller.pl line 118.
Global symbol "$db_dbse" requires explicit package name at the_biller.pl line 118.
Global symbol "$db_host" requires explicit package name at the_biller.pl line 118.
the_biller.pl has too many errors.
0
TZRickAuthor Commented:
First of all, am I connecting to the database properly?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

ozoCommented:
         print MAIL "TO: ".$cur_email;
          print MAIL "SUBJECT: Monthly Service Charge\n\n";
          my $billing = "<HTML><HEAD></HEAD>
should probably be
          print MAIL "TO: $cur_email\n";
          print MAIL "SUBJECT: Monthly Service Charge\n\n";
          my $billing = "<HTML><HEAD></HEAD>";
0
kanduraCommented:
first two things i noticed are:

- line 97 doesn't print a newline, so the email headers are likely to be wrong:

              print MAIL "TO: ".$cur_email . "\n";

- line 99 doesn't end the string, and has no ;

              my $billing = "<HTML><HEAD></HEAD>";
   
I assume you will be putting more into that string later on :-)

I'll take a stab at refactoring it a bit.
0
kanduraCommented:
just some more comments before i start messing up your script:

- I'd use DBI's fetchrow_hashref to get the billing_history records, instead of all the named variables. That way you get a single datastructure, which is far easier to pass around to other code
- I'd pull the stuff to fetch the email address out into a separate sub
- I'd pull the email sending stuff out into a separate sub as well

That way, the main loop should become more obvious:

    while(my $record = $sth->fetchrow_hashref) {
        if($record->{billing_amount} > 1.12) {
            $record->{store_email} = get_email_for_store( $record->{store_id} );
            send_service_mail($record);
        }
    }

That's assuming the column names in billing_history are the same as your variable names, of course.

0
kanduraCommented:
Here's a first iteration. It's not perfect, and I can think of a number of improvements, but I hope you'll agree that this version is more readable and easier to maintain than your original script.


#!/usr/bin/perl -w

#-----------------------------------------
# $Id$
#
# This script will generate the monthly
# bills to customers to pay through PayPal for website
#
#-----------------------------------------

use strict;
use Date::Format;
use DBI;


# open log channel
startlog();

logmsg( "Running\n" );

# Function 'time2str' is included
# in 'Date::Format'.
my $today = time2str("%Y-%m-%d", time);

# The first routine we will run will
my $stores = get_stores($today);

while( my $store = get_next_store($stores) ) {
    logmsg( "$store->{store_id} : $store->{user_type} : amount owed = $store->{billing_amount}\n" );

    # Now we have the amount that this store_id owes,
    # if it is greater than zero (0), we will send them an e-mail to pay through PayPal, otherwise we will just update their
    # 'due_date' for another month.
    if( $store->{billing_amount} > 1.12 ) {
        logmsg( "$store->{store_id} : will be charged.\n" );

        $store->{cur_email} = get_emailaddress_for_store($store->{store_id});
        send_service_email( $store );
    } else {
        # update due date
        update_duedate($store);
    }
}

# Subroutines
sub Database {

    # Connection info.
    my $db_type = "mysql";
    my $db_host = "hostname";
      #my $db_host = "localhost";
    my $db_sock = "/var/lib/mysql/mysql.sock";
    my $db_user = "username";
    my $db_pass = "password";
    my $db_dbse = "dbname";

    my $c = DBI->connect_cached("DBI:$db_type:$db_dbse:$db_host",
                  "$db_user",
                  "$db_pass",
                  { RaiseError => 1 } )
    || die "Cannot connect to DB server: $DBI::errstr .\n";

    return $c;
}

sub get_stores {
    my $today = shift;
    # grab all of the pertinent information
    # from the 'billing_history' table.
    my $query = "SELECT b.*
               FROM billing_history b
              WHERE b.due_date LIKE '$today %'
              AND b.payment_amount='0.00'";

    my $sth = Database()->prepare($query);
    $sth->execute();

    logmsg( "$query\n" );
    return $sth;
}

sub get_next_store {
    my $sth = shift;
    return $sth->fetchrow_hashref;
}

sub get_emailaddress_for_store {
    my $store_id = shift;

    # If this user owes any money, then we will have
    # to grab their credit card number from the DB.

    my $query = "     SELECT configuration_value AS email
                      FROM configuration
                      WHERE configuration.store_id=? AND configuration_key='OWNER_EMAIL'";

    my $ref = Database()->prepare_cached($query);

    logmsg(  "$store_id : activation reference = @$ref[0]\n" );

    $ref->execute($store_id);
    my ($email) = $ref->fetchrow_array();

    return $email;
}

sub update_duedate {}

sub send_service_email {
    my $store = shift;
    open(MAIL, "|/usr/sbin/sendmail -t");

    print MAIL "Content-type: text/html\n";
    print MAIL "FROM: accounts\@offersplus.com\n";
    print MAIL "TO: ".$store->{cur_email} . "\n";
    print MAIL "SUBJECT: Monthly Service Charge\n\n";
    my $billing = "<HTML><HEAD></HEAD>";
    print MAIL $billing;

    close(MAIL);
}

sub startlog {
    # Open Log File.
    open(LF, "+>", "./billing.log") || die "Failed to open Logfile: $!\n";
}

sub logmsg {
    # Function 'time2str' is included
    # in 'Date::Format'.
    my $today = time2str("%Y-%m-%d", time);
    print LF "$today - ", @_;    
}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TZRickAuthor Commented:
Thank you for your help! I believe I worked it out with your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.