?
Solved

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

Posted on 2005-03-30
8
Medium Priority
?
208 Views
Last Modified: 2010-03-05
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;
}
0
Comment
Question by:TZRick
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 18

Expert Comment

by:kandura
ID: 13666801
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
 
LVL 1

Author Comment

by:TZRick
ID: 13666847
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
 
LVL 1

Author Comment

by:TZRick
ID: 13666852
First of all, am I connecting to the database properly?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 84

Assisted Solution

by:ozo
ozo earned 80 total points
ID: 13666879
         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
 
LVL 18

Expert Comment

by:kandura
ID: 13666889
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
 
LVL 18

Expert Comment

by:kandura
ID: 13666994
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
 
LVL 18

Accepted Solution

by:
kandura earned 1920 total points
ID: 13670461
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
 
LVL 1

Author Comment

by:TZRick
ID: 13675721
Thank you for your help! I believe I worked it out with your help!
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question