Here is the script that I am using. Remember that the script creates the file
perfectly and stores the file with data in a folder on the server. The emailed copy is
being sent out blank.
Thanks,
JC
#!/usr/bin/perl
use strict;
use DBI;
use lib '/home/samcsm/jason/myperl/lib/perl5/site_perl/';
use MIME::Lite;
our ($dbh); #
our (@files); #
our ($filename); #
our ($errorlog); #logs all successes and failures of the report
our ($dirfolder); #where the treatment report is sent prior to emailing
our (@record); #holds all records returned from query
our ($success_var); #flags the script if completion was successful or not
our ($sql); #
our ($sth); #
our ($dayreport); #
our ($process_date); #used in the log file to tell what date and time a file complete
or failed
our ($month); #
our ($day); #
our ($year); #
our ($truename); #Time stamps the name of each treatment report AFTER the GENERIC name
is changed
our (@times); #Helps Timestamp the treatment reports each day
our ($reportlog); #Folder for keeping up with all Treatment reports
our ($fourdayreport); #the path that leads to the location of the generic 4 day report
our ($recordlist); #allows the for manipulation of data retrieved from query
#set up for the file naming covention
@times = (localtime)[3..5]; # grabs year/month/day values
$month = $times[1] + 1;
$day = $times[0];
$year = $times[2] + 1900;
$truename = "Tr0"."$month"."0"."$day"."$year"."_4day.csv\n"; #Time stamps each file
once generic file is renamed
$errorlog ="/home/samcsm/jason/Treatlogs/errorlog.txt"; #Location and Generic name
for logging treatment report
$reportlog="/home/samcsm/jason/Treatlogs/"; #Location for storing fourday treatment
reports
$dirfolder="/home/samcsm/treatment/"; #Location for fourday treatment report prior
to emailing out
$fourdayreport = "/home/samcsm/treatment/4daytreatment.csv"; #Location and Generic
name for fourday treatment report
$ENV{"ORACLE_HOME"}="/orav101/oracle/8.0.6";
#Gather data for connecting to database:
#Connecting to the database:database=<database_name1','$username','$passwd'
#login($database_temp, $username, $passwd);
#Set up your sql statement that you want to run in Oracle
$sql=qq(select * from some_table where rownum <1000);
$sth=$dbh->prepare($sql); #Prepare the SQL statement for running and it'll be
stored in Oracle buffer
$sth->execute(); #Execute the SQL statememt
dateme(); #call dateme function and store value in $process_date
$success_var = data_collect(); #call data_collection function and store the word
"success"
#or "failed" in $success_var variable
my $logvariable = logresults($success_var); #call the logresults function and send
the "success" or "failed"
mailman(); #call mailman function
print "$logvariable\n"; #print the results returned from logresults function
close(OUTFILE); #close file for errorlog and query
close(ERRORLOG);
$dbh->disconnect; #disconnet
#-----------------------SUB ROUTINE FOR COLLECTING
DATA-----------------------------------------------------------------------
sub data_collect {unless (open(OUTFILE,">$fourdayreport")) #open 4daytreatment.csv
{die open(ERRORLOG, ">>$errorlog") && #or die and open errorlog.txt
print ERRORLOG "Sorry file $dayreport couldn't be created\n"; return "Failed";}
#print into errorlog.txt message
else{
while( my @record= $sth->fetchrow_array()) #while theres data, collect
{
$recordlist=join(",",@record); #separate records with a "," and store
print OUTFILE "$recordlist\n"; #print to newformatted records to
#the generic file 4daytreatment.csv
}return "success"; #send back the word "success" when done
}
#-----------------------SUB ROUTINE for Error
Checkin-------------------------------------------------------------------------
#-----------------------LOGFILE SUB
ROUTINE--------------------------------------------------------------------------------------
sub logresults {
if ($success_var eq "success") #Was query completed successfully?
{open(ERRORLOG, ">>$errorlog") && #If so, open errorlog.txt and print
print ERRORLOG "\n$process_date\nQuery was a $success_var!!!\n"; return 1;} #date and
success message
else
{open(ERRORLOG, ">>$errorlog")&& #If query failed open errorlog.txt
print ERRORLOG "\n$process_date\nThe Treatment report $success_var!!!\n";return 0;}
#and print date and failure message
}#end log results subroutine
#-----------------------SUB ROUTINE for Time
Stamp-------------------------------------------------------------------------
sub dateme{
$process_date=localtime(); #call localtime funcion and store as scalar value
}return $process_date; #return scalar value of the localtime function
#-----------------------Email SUB
ROUTINE-----------------------------------------------------------------------------------------
sub mailman {
my $disclaimer = qq( Type => 'TEXT',
Data =>'This email and all attached files are automatically generated daily by
WNP. If there are any problems with the reports or delivery of reports, please contact
the CSM on call person at 123-456-7890.');
my $message=MIME::Lite->build(
From =>'[EMAIL PROTECTED]',
To =>'[EMAIL PROTECTED]',
#Cc =>
Subject =>'Four Day Out Report',
Type =>'multipart/mixed',
);
$message->attach ( Type => 'TEXT',
Data =>'This email and all attached files are automatically generated daily by
WNP. If there are any problems with the reports or delivery of reports, please contact
the CSM on call person at 123-456-7890.');
$message->attach (
Path =>'/home/samcsm/treatment/4daytreatment.csv',
type =>'File/.csv',
Filename =>$truename,
Disposition =>'attachment',
Encoding => 'base64'
);
$message->send;
return;
}#end of mailman subroutine
#-----------------------End of
Script---------------------------------------------------------------------------------------------
}
Jenda Krynicky <[EMAIL PROTECTED]> wrote:
From: jason corbett
> I am sending Excel and .csv files via MIME and it appears that the
> file is created on the server with the needed data, but when the
> script is supposed to email a copy, I am getting blank .csv files.
Just a wild guess ... are you closing the .csv or Excel files before
trying to send them? I've heard from someone who was trying to send
some Excel files created with Spreadsheet::WriteExcel and was
receiving them empty or incomplete and the problem was that he did
not close the files first so some of the data was not in the file
yet.
If this is not the problem show us a piece of code.
Jenda
===== [EMAIL PROTECTED] === http://Jenda.Krynicky.cz =====
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
