Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

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;
}
0
TZRick
Asked:
TZRick
  • 4
  • 3
2 Solutions
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
TZRickAuthor Commented:
Thank you for your help! I believe I worked it out with your help!
0

Featured Post

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!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now