How to append year and monthyear combination to filename generated
In reference to https://www.experts-exchange.com/questions/29079671/How-to-generate-multiple-files-from-a-perl-script.html
The related question was assisted by wilcoxon and his expertise on Perl!
For the files from the do_dataset, how would I append the below to the end of the files generated from the do_dataset?
For files 1 - 4: append "_MonYY" to filename (example Detail_ESIID --> Detail_ESIID_Dec17)
For file 5: append "MonYY" to the filename (example RecLoad --> RecLoadDec17 )
For files 1 - 4: append "_YYYY" to filename (example Detail_ESIID --> Detail_ESIID_2017)
For file 5: append "YYYY" to the filename (Recload --> RecLoad2017)
The year and MonYY would be derived from firstday and lastday
For (example firstday = 20100101 for Jan 1, 2010, lastday = 20101231 for Dec 31, 2010)
The YYYY would be 2010 and MonYY would be Jan10 for firstday
Here's the code snippet:
The related question was assisted by wilcoxon and his expertise on Perl!
For the files from the do_dataset, how would I append the below to the end of the files generated from the do_dataset?
For files 1 - 4: append "_MonYY" to filename (example Detail_ESIID --> Detail_ESIID_Dec17)
For file 5: append "MonYY" to the filename (example RecLoad --> RecLoadDec17 )
For files 1 - 4: append "_YYYY" to filename (example Detail_ESIID --> Detail_ESIID_2017)
For file 5: append "YYYY" to the filename (Recload --> RecLoad2017)
The year and MonYY would be derived from firstday and lastday
For (example firstday = 20100101 for Jan 1, 2010, lastday = 20101231 for Dec 31, 2010)
The YYYY would be 2010 and MonYY would be Jan10 for firstday
Here's the code snippet:
use strict;
use warnings;
use DBI;
use Date::Manip;
Date_Init('TZ=US/Central','Internal=1');
$| = 1;
my $nodata = 0;
my ($db, $user, $pw, $wkdir, $firstday, $lastday) = @ARGV;
# Display parameter values passed by the appworx
print("\n");
print("Database Instance : $db \n");
print("Connected as User : $user \n");
print("Output Directory is : $wkdir \n");
print("First Day : $firstday \n");
print("Last Day : $lastday \n\n");
#Check to see if required parameters are received by appworx
die("\n\n - ERROR: The Required number of arguments were not received...\n\n") unless $lastday;
my ($fyr, $fmon, $fday, $lyr, $lmon, $lday);
if ($firstday =~ /^(\d\d\d\d)(\d\d)(\d\d)$/) {
($fyr, $fmon, $fday) = ($1, $2, $3);
} else {
die " - ERROR: firstday ($firstday) is not a date\n";
}
if ($lastday =~ /^(\d\d\d\d)(\d\d)(\d\d)$/) {
($lyr, $lmon, $lday) = ($1, $2, $3);
} else {
die " - ERROR: lastday ($lastday) is not a date\n";
}
# Connectivity check to the database else display failed connectivity
my $dbh = DBI->connect("dbi:Oracle:$db", $user, $pw, { AutoCommit => 0, RaiseError => 1 }) or die("Cannot connect to Database '$db' " . DBI->errstr());
# Capture counts for distinct partitions for given firstday and lastday dates
my $partition_key = "${firstday}_$lastday";
print("Partition Key : $partition_key \n\n");
# Get value of rowcount returned from Count of partition keys from REGION table
my ($rowcount) = $dbh->selectrow_array("select count(distinct r.execution_id) from REGION r where r.partition_key = '$partition_key' AND r.status = 'N'");
# Check if single partition exists for a given firstday/lastday parameters, ROW COUNT RETURNED will be 1 and then proceed with extract out columns from the table to a csv file
# IF multiple partitions exist OR no data exists for a given firstday/last day set - the PROCESS WILL ABORT
die "ERROR: NO DATA EXISTS FOR GIVEN FIRSTDAY LASTDAY SET OF DATES - ABORTING!!!\n\n"
unless $rowcount;
die "ERROR: UNREPORTED DATA EXISTS FOR THE GIVEN FIRSTDAY LASTDAY SET OF DATES - ABORTING!!!\n\n"
if ($rowcount > 1);
do_dataset('Detail_ESIID', [qw(ESIID REPCODE REPNAME TO_CHAR(STARTTIME,'MM/DD/YYYY') TRADEDATE ORIGIN INTV1 INTV2 INTV3 INTV4 INTV5 INTV6 INTV7 INTV8 INTV9 INTV10 INTV11 INTV12 INTV13 INTV14 INTV15 INTV16 INTV17 INTV18 INTV19 INTV20 INTV21 INTV22 INTV23 INTV24 INTV25 INTV26 INTV27 INTV28 INTV29 INTV30 INTV31 INTV32 INTV33 INTV34 INTV35 INTV36 INTV37 INTV38 INTV39 INTV40 INTV41 INTV42 INTV43 INTV44 INTV45 INTV46 INTV47 INTV48 INTV49 INTV50 INTV51 INTV52 INTV53 INTV54 INTV55 INTV56 INTV57 INTV58 INTV59 INTV60 INTV61 INTV62 INTV63 INTV64 INTV65 INTV66 INTV67 INTV68 INTV69 INTV70 INTV71 INTV72 INTV73 INTV74 INTV75
INTV76 INTV77 INTV78 INTV79 INTV80 INTV81 INTV82 INTV83 INTV84 INTV85 INTV86 INTV87 INTV88 INTV89 INTV90 INTV91 INTV92 INTV93 INTV94 INTV95 INTV96 INTV97 INTV98 INTV99 INTV100 DAILY_TOTAL)], status => 'N');
# quick hack that only handles monthly and yearly
my $date;
if ($fyr == $lyr and $fmon == $lmon) {
$date = month_name($fmon) . substr($fyr,0,2);
} elsif ($fyr == $lyr) {
$date = $fyr;
} else {
die " - ERROR: don't know what to do if years don't match\n";
}
do_dataset('Monthly_ESIID', [qw(ESIID REPCODE REPNAME SUM(TOTAL) MONTHLY_TOTAL)] , group_by => [qw(ESIID REPCODE REPNAME)]);
do_dataset('Daily_REP', [qw(REPNAME REPCODE TO_CHAR(STARTTIME,'MM/DD/YYYY') TRADEDATE SUBTRACT_TOTAL ADJ_TOTAL)]);
do_dataset('Monthly_REP', [qw(REPNAME REPCODE TO_CHAR(STARTTIME,'MM/DD/YYYY') TRADEDATE SUBTRACT_TOTAL ADJ_TOTAL)], group_by => [qw(REPCODE REPNAME)]);
do_dataset('RecLoad', [qw(TO_CHAR(STARTTIME,'MM/DD/YYYY') TRADEDATE DUNSNUMBER REPNAME ADJ_TOTAL)], group_by => [qw(STARTIME DUNSNUMBER REPNAME)]);
# Disconnect from the database
$dbh->disconnect();
sub do_dataset {
my ($file, $cols, %opt) = @_;
# do some pre-processing of options
my $col = join ',', @$cols;
my $group = '';
if (exists $opt{group_by}) {
$group = join ', ', @{$opt{group_by}};
delete $opt{group_by};
}
# XXX - changed to use passed-in value
# will work for numbers - requires passed in string value to be quoted
my $xtra = %opt ? ('and ' . join(' and ', map { "$_ = $opt{$_}" } keys %opt)) : '';
# get data
my $allrows = $dbh->selectall_arrayref("SELECT $col FROM REGION WHERE PARTITION_KEY = '$partition_key' $xtra $group")
or die("Could not get data from table REGION ".DBI->errstr);
# dump the data out to a file
my $dumpdir = "$wkdir/$file.csv";
open OUT, '>', $dumpdir or die " - Could not open $dumpdir for export. $!";
print OUT $col, "\n"; # manually list column names from input
print OUT map { print join(',', @$_), "\n" } @$allrows;
close OUT;
# Updates the STATUS column to R(Reported) for the partition key after REC LOAD csv outfile is generated
print "Updating data set status to R (reported) for partition key $partition_key. \n\n";
$dbh->do("UPDATE REGION SET STATUS = 'R', LSTIME = SYSDATE WHERE PARTITION_KEY = '$partition_key' $xtra")
or die "Could not get data from table REGION ".DBI->errstr;
# File output process completion Notification
print "The $dumpdir file is generated to output directory.\n\n";
}ASKER
Thanks for suggestions. Am able to append the filenames correctly now. Still have some redundant code to eliminate but the process works. Below is the working code...
Note: Lines 88 thru 115 have few lines of duplicated code. Any suggestion on how to get rid of the duplicate lines of code here or consolidate them?
Note: Lines 88 thru 115 have few lines of duplicated code. Any suggestion on how to get rid of the duplicate lines of code here or consolidate them?
use strict;
use warnings;
use DBI;
use Date::Manip;
Date_Init('TZ=US/Central','Internal=1');
$| = 1;
my $nodata = 0;
my ($db, $user, $pw, $wkdir, $firstday, $lastday) = @ARGV;
# Display parameter values passed by the appworx
print("\n");
print("Database Instance : $db \n");
print("Connected as User : $user \n");
print("Output Directory is : $wkdir \n");
print("First Day : $firstday \n");
print("Last Day : $lastday \n\n");
#Check to see if required parameters are received by appworx
unless($db && $user && $pw && $wkdir && $firstday && $lastday)
{
die("\n\n - ERROR: The Required number of arguments were not received. Verify that the AppWorx prompts are correct and reset the module..\n\n");
}
# Connectivity check to the database else display failed connectivity
my $dbh = DBI->connect("dbi:Oracle:$db", $user, $pw, { AutoCommit => 0, RaiseError => 1 }) or die("Cannot connect to Database '$db' " . DBI->errstr());
# Capture counts for distinct partitions for given firstday and lastday dates
my $partition_key = join '', $firstday ,'_',$lastday;
print("Partition Key : $partition_key \n\n");
my $sthcounts = $dbh->prepare(qq(select count(distinct r.partition_key) from REGION r where r.partition_key = ? AND r.status = 'N' ));
# Values of partition key will be passed on by appworx will be parameterized in the script
$sthcounts -> execute($partition_key);
# Get value of rowcount returned from Count of partition keys from REGION table
my $rowcount = ($sthcounts-> fetchrow_array());
$sthcounts -> finish();
# Check if single partition exists for a given firstday/lastday parameters, ROW COUNT RETURNED will be 1 and then proceed with extract out columns from the table to a csv file
# IF multiple partitions exist OR no data exists for a given firstday/last day set - the PROCESS WILL ABORT
die "ERROR: NO DATA EXISTS FOR GIVEN FIRSTDAY LASTDAY SET OF DATES - ABORTING!!!\n\n"
unless $rowcount;
die "ERROR: UNREPORTED DATA EXISTS FOR THE GIVEN FIRSTDAY LASTDAY SET OF DATES - ABORTING!!!\n\n"
if ($rowcount > 1);
# Datasets where each data set is the file to be processed. The script will output a total of 5 files.
do_dataset('Detail_ESIID_', [qw(ESIID,REPCODE, REPNAME, TO_CHAR(STARTTIME,'MM/DD/YYYY') as tradedate, ORIGIN, INTV1,INTV2,INTV3, INTV4, INTV5, INTV6, INTV7, INTV8, INTV9, INTV10, INTV11, INTV12, INTV13, INTV14, INTV15, INTV16, INTV17, INTV18, INTV19, INTV20,
INTV21,INTV22,INTV23,INTV24,INTV25,INTV26,INTV27,INTV28,INTV29,INTV30,INTV31,INTV32,INTV33,INTV34, INTV35, INTV36, INTV37, INTV38, INTV39, INTV40, INTV41, INTV42, INTV43, INTV44, INTV45, INTV46, INTV47, INTV48, INTV49, INTV50,
INTV51,INTV52,INTV53,INTV54,INTV55,INTV56,INTV57,INTV58,INTV59,INTV60,INTV61,INTV62,INTV63,INTV64, INTV65, INTV66, INTV67, INTV68, INTV69, INTV70, INTV71, INTV72, INTV73, INTV74, INTV75,
INTV76,INTV77,INTV78,INTV79,INTV80,INTV81,INTV82,INTV83,INTV84,INTV85,INTV86,INTV87,INTV88,INTV89, INTV90, INTV91, INTV92, INTV93, INTV94, INTV95, INTV96, INTV97, INTV98, INTV99, INTV100, TOTALS)], status => "'N'");
do_dataset('Monthly_ESIID_', [qw(ESIID, REPCODE, REPNAME, SUM(TOTALS) as TOTALS)] , group_by => [qw(GROUP BY ESIID, REPCODE, REPNAME)]);
do_dataset('Daily_REP_', [qw(REPNAME, REPCODE, TO_CHAR(STARTTIME,'MM/DD/YYYY') as tradedate, SUM(TOTALS), SUM(SUBTRACT_TOTAL), SUM(ADJ_TOTAL))], group_by => [qw(GROUP BY REPNAME, REPCODE, TO_CHAR(STARTTIME,'MM/DD/YYYY'))]);
do_dataset('Monthly_REP_', [qw(REPNAME, REPCODE, TO_CHAR(STARTTIME,'MM/DD/YYYY') as tradedate, SUM(SUBTRACT_TOTAL), SUM(ADJ_TOTAL))], group_by => [qw(GROUP BY REPNAME, REPCODE, TO_CHAR(STARTTIME,'MM/DD/YYYY'))]);
do_dataset('RecLoad_', [qw(TO_CHAR(STARTTIME,'MM/DD/YYYY') as tradedate, REPNAME, DUNSNUMBER, SUM(ADJ_TOTAL) AS TOTALS)], group_by => [qw(GROUP BY REPNAME, DUNSNUMBER, TO_CHAR(STARTTIME,'MM/DD/YYYY'))]);
# Subroutine does processing of the 5 datasets
sub do_dataset
{
my ($file, $cols, %opt) = @_;
my $col = join ' ', @$cols;
my $group = ' ';
# process the below if dataset has a group by
if (exists $opt{group_by})
{
$group = join ' ', @{$opt{group_by}};
delete $opt{group_by};
}
# The below statement is placeholder to hold extra clauses or conditions, for instance dataset1 would have print a value of STATUS = 'N'
my $extra = %opt ? ('and ' . join(' and ', map { "$_ = $opt{$_}" } keys %opt)) : '';
# Capture dates to append to filenames created
my $firstmonth = substr($firstday,4,2);
my $lastmonth = substr($lastday,4,2);
my $firstyear = substr($firstday,0,4);
my $lastyear = substr($lastday,0,4);
if (($firstmonth == '01' and $lastmonth == '12') and ($firstyear == $lastyear))
{
my $part_key = substr($firstday,0,4);
my $getfilename = join '',$file, $part_key,'.csv';
my $allrows = $dbh->selectall_arrayref("SELECT $col FROM REGION WHERE PARTITION_KEY = '$partition_key' $extra $group")
or die("Could not get data from table REGION ".DBI->errstr);
my $dumpdir = "$wkdir/$getfilename";
open OUT, '>', $dumpdir or die " - Could not open $dumpdir for export. $!";
print OUT $col, "\n"; # manually list column names from input
print OUT map {join(',', map { defined($_) ? $_ : '' } @$_), "\n" } @$allrows;
close OUT;
# File output process completion
print "The $dumpdir file is generated to output directory.\n\n";
}
else
{
my $part_key = join '',substr($firstday,4,2), substr($firstday,0,4),'.csv';
my $getfilename = join '',$file, $part_key,'.csv';
my $allrows = $dbh->selectall_arrayref("SELECT $col FROM REGION WHERE PARTITION_KEY = '$partition_key' $extra $group") or die("Could not get data from table REGION ".DBI->errstr);
my $dumpdir = "$wkdir/$getfilename";
open OUT, '>', $dumpdir or die " - Could not open $dumpdir for export. $!";
print OUT $col, "\n"; # manually list column names from input
print OUT map {join(',', map { defined($_) ? $_ : '' } @$_), "\n" } @$allrows;
close OUT;
# File output process completion
print "The $dumpdir file is generated to output directory.\n\n";
}
}
# Updates the STATUS column to R(Reported) for the partition key after csv outfiles are generated
print "Updating data set status to R (reported) for partition key $partition_key. \n\n";
my $update_status = $dbh->prepare(qq(UPDATE REGION SET STATUS = 'R', LSTIME = SYSDATE WHERE PARTITION_KEY = ? AND STATUS = 'N'))
or die("Could not UPDATE/ACCESS the table '$_'".DBI->errstr);
$update_status -> execute($partition_key);
$update_status -> finish();
# Disconnect from the database
$dbh->disconnect();ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the pointers on improvizing the code.
It is inconsistent. Use a the same approach for all types. Use the same separator in all cases. I prefer the hyphen over underscore.
Then consider using lexical sortable formats. Thus consider using filename-YYYY{MM|MMM}.