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:

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";
}

Open in new window

steve2312Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Just a comment:

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)

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}.
0
steve2312Author Commented:
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?



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();

Open in new window

0
FishMongerCommented:
First step is to clean up your formatting.  Your indentation is inconsistent which makes it difficult to follow.  The easiest way to fix the formatting is to use Perl::Tidy.

Then work on fixing the following warnings your code produces.
c:\test>perl -c steve2312.pl
Possible attempt to separate words with commas at steve2312.pl line 71.
Possible attempt to separate words with commas at steve2312.pl line 77.
Possible attempt to separate words with commas at steve2312.pl line 78.
Possible attempt to separate words with commas at steve2312.pl line 83.
Possible attempt to separate words with commas at steve2312.pl line 85.
Possible attempt to separate words with commas at steve2312.pl line 90.
Possible attempt to separate words with commas at steve2312.pl line 92.
Possible attempt to separate words with commas at steve2312.pl line 97.
Possible attempt to separate words with commas at steve2312.pl line 100.
steve2312.pl syntax OK

When writing to the csv file, you should use the Text::CSV_XS module instead of your current print map statement.

Instead of passing positional args to the script I'd use named args which are parsed by the Getopt::Long module and in addition to that I'd use Pod::Usage.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
steve2312Author Commented:
Thanks for the pointers on improvizing the code.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.