Data to Drive the Layout of Report 1

[V00D00]

I am going to have a very hard time articulating this problem so forgive me if this is very confusing.

I have the following code

#!/usr/local/bin/perl
use warnings;
use strict;
use DBI;
use Mail::Sender;

$ENV{DBPATH} = "/usr/task";
$ENV{DBF} = "/usr/task/task.dbs";
$ENV{DB} = "task";
$ENV{DBTEMP} = "/tmp";
$ENV{INFORMIXDIR} = "/usr/informix";
$ENV{PATH} = "$ENV{PATH}:/usr/local/bin:/usr/informix/bin";
my $wkd ="/usr/task/leadcount/";

my @week1_nums = ();		# array of numbers
my %week1_freq = ();		# has of number frequencies
my @week2_nums = ();		# array of numbers
my %week2_freq = ();		# has of number frequencies
my @week3_nums = ();		# array of numbers
my %week3_freq = ();		# has of number frequencies
my @week4_nums = ();		# array of numbers
my %week4_freq = ();		# has of number frequencies

my $todaysdate = (time - 25200);
my ($todaysec,$todaymin,$todayhour,$todayday,$todaymon,
$todayyear,$todaywday,$todayyday,$todayisdst) = gmtime ($todaysdate);

$todaymon = $todaymon + 1;
$todayyear = $todayyear + 1900;

my $today = "$todaymon/$todayday/$todayyear";
my $tdate = sprintf("%02d%02d%04d", (split('/',$today)));

my $week1_begin = (time - 543600);
my $week1_end = (time - 25200);

	my ($week1_begin_sec,$week1_begin_min,$week1_begin_hour,
	$week1_begin_day,$week1_begin_mon,$week1_begin_year,
	$week1_begin_wday,$week1_begin_yday,
	$week1_begin_isdst) = gmtime ($week1_begin);

	my ($week1_end_sec,$week1_end_min,$week1_end_hour,
	$week1_end_day,$week1_end_mon,$week1_end_year,
	$week1_end_wday,$week1_end_yday,
	$week1_end_isdst) = gmtime ($week1_end);

	$week1_begin_mon = $week1_begin_mon + 1;
	$week1_begin_year = $week1_begin_year + 1900;

	$week1_end_mon = $week1_end_mon + 1;
	$week1_end_year = $week1_end_year + 1900;


my $week1_startdate = "$week1_begin_mon/$week1_begin_day/$week1_begin_year";
my $week1_enddate = "$week1_end_mon/$week1_end_day/$week1_end_year";

$week1_startdate = sprintf("%02d/%02d/%04d",(split('/',$week1_startdate)));
$week1_enddate = sprintf("%02d/%02d/%04d",(split('/',$week1_enddate)));

# Establish the Beginning and Ending Dates that are two weeks old.

my $week2_begin = (time - 1148400);
my $week2_end = (time - 630000);

	my ($week2_begin_sec,$week2_begin_min,$week2_begin_hour,
	$week2_begin_day,$week2_begin_mon,$week2_begin_year,
	$week2_begin_wday,$week2_begin_yday,
	$week2_begin_isdst) = gmtime ($week2_begin);

	my ($week2_end_sec,$week2_end_min,$week2_end_hour,
	$week2_end_day,$week2_end_mon,$week2_end_year,
	$week2_end_wday,$week2_end_yday,
	$week2_end_isdst) = gmtime ($week2_end);

	$week2_begin_mon = $week2_begin_mon + 1;
	$week2_begin_year = $week2_begin_year + 1900;

	$week2_end_mon = $week2_end_mon + 1;
	$week2_end_year = $week2_end_year + 1900;	


my $week2_startdate = "$week2_begin_mon/$week2_begin_day/$week2_begin_year";
my $week2_enddate = "$week2_end_mon/$week2_end_day/$week2_end_year";

$week2_startdate = sprintf("%02d/%02d/%04d",(split('/',$week2_startdate)));
$week2_enddate = sprintf("%02d/%02d/%04d",(split('/',$week2_enddate)));

# Establish the Beginning and Ending Dates that are three weeks old.

my $week3_begin = (time - 1753200);
my $week3_end = (time - 1234800);

	my ($week3_begin_sec,$week3_begin_min,$week3_begin_hour,
	$week3_begin_day,$week3_begin_mon,$week3_begin_year,
	$week3_begin_wday,$week3_begin_yday,
	$week3_begin_isdst) = gmtime ($week3_begin);

	my ($week3_end_sec,$week3_end_min,$week3_end_hour,
	$week3_end_day,$week3_end_mon,$week3_end_year,
	$week3_end_wday,$week3_end_yday,
	$week3_end_isdst) = gmtime ($week3_end);

	$week3_begin_mon = $week3_begin_mon + 1;
	$week3_begin_year = $week3_begin_year + 1900;

	$week3_end_mon = $week3_end_mon + 1;
	$week3_end_year = $week3_end_year + 1900;	

my $week3_startdate = "$week3_begin_mon/$week3_begin_day/$week3_begin_year";
my $week3_enddate = "$week3_end_mon/$week3_end_day/$week3_end_year";

$week3_startdate = sprintf("%02d/%02d/%04d",(split('/',$week3_startdate)));
$week3_enddate = sprintf("%02d/%02d/%04d",(split('/',$week3_enddate)));

# Establish the Beginning and Ending Dates that are 4 weeks old.

my $week4_begin = (time - 2358000);
my $week4_end = (time - 1839600);

	my ($week4_begin_sec,$week4_begin_min,$week4_begin_hour,
	$week4_begin_day,$week4_begin_mon,$week4_begin_year,
	$week4_begin_wday,$week4_begin_yday,
	$week4_begin_isdst) = gmtime ($week4_begin);

	my ($week4_end_sec,$week4_end_min,$week4_end_hour,
	$week4_end_day,$week4_end_mon,$week4_end_year,
	$week4_end_wday,$week4_end_yday,
	$week4_end_isdst) = gmtime ($week4_end);

	$week4_begin_mon = $week4_begin_mon + 1;
	$week4_begin_year = $week4_begin_year + 1900;

	$week4_end_mon = $week4_end_mon + 1;
	$week4_end_year = $week4_end_year + 1900;	

my $week4_startdate = "$week4_begin_mon/$week4_begin_day/$week4_begin_year";
my $week4_enddate = "$week4_end_mon/$week4_end_day/$week4_end_year";

$week4_startdate = sprintf("%02d/%02d/%04d",(split('/',$week4_startdate)));
$week4_enddate = sprintf("%02d/%02d/%04d",(split('/',$week4_enddate)));


my $dbh = DBI->connect('task','','','AtomixDBD');

my $week1_sql =	"select lead_src ".
				"from outbound ".
				"WHERE (listid between \"1000\" and \"7599999\") ".
				"and (load_date between \"$week1_startdate\" and \"$week1_enddate\") ".
				"and (status = \"A\" or status = \"B\" ".
				"or status = \"LM\" or status = \"N\" or status = \"NA\" ".
				"or status = \"NC\" or status = \"SK\") ".
				"order by lead_src";

my $week2_sql =	"select lead_src ".
				"from outbound ".
				"WHERE (listid between \"1000\" and \"7599999\") ".
				"and (load_date between \"$week2_startdate\" and \"$week2_enddate\") ".
				"and (status = \"A\" or status = \"B\" ".
				"or status = \"LM\" or status = \"N\" or status = \"NA\" ".
				"or status = \"NC\" or status = \"SK\") ".
				"order by lead_src";
			
my $week3_sql =	"select lead_src ".
				"from outbound ".
				"WHERE (listid between \"1000\" and \"7599999\") ".
				"and (load_date between \"$week3_startdate\" and \"$week3_enddate\") ".
				"and (status = \"A\" or status = \"B\" ".
				"or status = \"LM\" or status = \"N\" or status = \"NA\" ".
				"or status = \"NC\" or status = \"SK\") ".
				"order by lead_src";

my $week4_sql =	"select lead_src ".
				"from outbound ".
				"WHERE (listid between \"1000\" and \"7599999\") ".
				"and (load_date between \"$week4_startdate\" and \"$week4_enddate\") ".
				"and (status = \"A\" or status = \"B\" ".
				"or status = \"LM\" or status = \"N\" or status = \"NA\" ".
				"or status = \"NC\" or status = \"SK\") ".
				"order by lead_src";

open(FH,">$wkd$tdate.leadcount.txt");
print FH "COUNT SUMMARY\n\n";
print FH "\t\t\t\tWEEK1\tWEEK2\tWEEK3\tWEEK4\n";
print FH "\t\t\t\t-----\t-----\t-----\t-----\n\n";

#
# WEEK 1 QUERY AND COUNTS
#

my $week1_psql = $dbh->prepare($week1_sql);
my $week1_rc = $week1_psql->execute;
while (@week1_nums = $week1_psql->fetchrow) {
	$week1_freq{$week1_nums[0]}++;
	}

$week1_psql->finish;

#
# WEEK 2 QUERY AND COUNTS
#

my $week2_psql = $dbh->prepare($week2_sql);
my $week2_rc = $week2_psql->execute;
while (@week2_nums = $week2_psql->fetchrow) {
	$week2_freq{$week2_nums[0]}++;
	}

$week2_psql->finish;

#
#WEEK 3 QUERY AND COUNTS
#

my $week3_psql = $dbh->prepare($week3_sql);
my $week3_rc = $week3_psql->execute;
while (@week3_nums = $week3_psql->fetchrow) {
	$week3_freq{$week3_nums[0]}++;
	}

$week3_psql->finish;

#
# WEEK 4 QUERY AND COUNTS
#

my $week4_psql = $dbh->prepare($week4_sql);
my $week4_rc = $week4_psql->execute;
while (@week4_nums = $week4_psql->fetchrow) {
	$week4_freq{$week4_nums[0]}++;
	}

$week4_psql->finish;

#
# DISCONNECT DATABASE
#

$dbh->disconnect;

undef $dbh;

#
# PRINT REPORT
#


print FH "\n\nCOUNTS FOR 4 WEEKS: \t\t";
	if (exists $week1_freq{'00'}) { print FH "$week1_freq{'00'}"};
	print FH "\t";
	if (exists $week2_freq{'00'}) { print FH "$week2_freq{'00'}"};
	print FH "\t";
	if (exists $week3_freq{'00'}) { print FH "$week3_freq{'00'}"};
	print FH "\t";
	if (exists $week4_freq{'00'}) { print FH "$week4_freq{'00'}"};

print FH "\n\n";

close (FH);

Right now this code works. But everytime there is a new "lead_src" added to the database, I will have to add an if statement to print it if it exists.

I would like to have to report keep the same layout, but not have to use if statements to print.

Example

COUNT SUMMARY

LEAD_SRC             WEEK1   WEEK2   WEEK3   WEEK4
--------             -----   -----   -----   -----
00                     1       3       2       4  
01                     3       4       3       2
02                     2       3       6       8

I hope someone understands what I am trying to ask...

KevinADC help me.

 

[KevinADC]

you need to study up on hashes, using sub routines and loops. That will cut out all that repetitive code you have and make your scripts much more scalable.

 

[stevexff]

I'd second KevinADCs suggestions. Wherever you find yourself cutting and pasting code and making minor changes to it (like the calculation of the weeks), you are doing it wrong. It makes your program big, hard to understand, and even harder to maintain. Factor out common code into subroutines and loops.

Also, the DBMS (in this case Informix) will do most of the donkey work for you. Presently, you are reading all the qualifying rows, and counting them. Worse still, you are doing all the date calculations the hard way.

The SQL COUNT(*) function will count the number of qualifying rows. The SQL GROUP BY clause will split those counts by LEAD_SRC. The SQL TODAY function (with some arithmetic and the UNITS parameter) will handle your date selection, and also in conjunction with the CASE clause to recode the week numbers. No time to post a sensible example, I may be able to do it tonight.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]