Link to home
Start Free TrialLog in
Avatar of TZRick
TZRick

asked on

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;
}
Avatar of kandura
kandura

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.
Avatar of TZRick

ASKER

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.
Avatar of TZRick

ASKER

First of all, am I connecting to the database properly?
SOLUTION
Avatar of ozo
ozo
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TZRick

ASKER

Thank you for your help! I believe I worked it out with your help!