Date::Calc and comparing across arrays?

[rcotten]

Hi monks... been using perl for a while now but this one has got me stumped. I have multiple lines in a .csv they all have start stop dates and total time on each line. I need to look for dates (across multiple lines) that are consecutive. accounting for weekend and holidays.

IE. input.csv

employid, start, stop, total
1234, 01/04/2013, 05/04/2013, 5
1234, 08/04/2013, 12/04/2013, 5
1234, 18/04/2013, 18/04/2013, 1
5678, 01/01/2013, 01/01/2013, 1

  output.csv

employid, start, stop, total
1234, 01/04/2013, 12/04/2013, 10
1234, 18/04/2013, 18/04/2013, 1

I have the open/close and csv reading fine with CSV::Text. I am pretty confident on the looping etc.. I'm just not sure the best way to look through file for a matching employid and then take into account weekends and holidays. Suggestions on modules and how to proceed?

Was seeing a lot of talk about Date::Calc..

Thanks ray

[hdb]

What the helpful Anonymous Monk probably wanted to say (but I might be wrong) is that a bit more evidence of effort on your side would get you more help here. If you had posted some code snippets like

use strict;
use warnings;
use Date::Calendar::Profiles qw( $Profiles );
use Date::Calendar;

my $cal = Date::Calendar->new( $Profiles->{'US-AZ'} );
print "Number of work days between 5 Apr 13 and 8 Apr 13:";
print $cal->delta_workdays( 2013, 4, 5,
                2013, 4, 8,
                0, 0 );

people would be providing more detailed help. Otherwise, there is often the assumption that you had not bothered to look up the massive amount of documentation on Perl modules available on CPAN and to spend some time experimenting.

With Date::Calendar and Date::Calc you seem to be on the safe side.

[rcotten]

Understood

This is what I have so far...

#!/usr/bin/perl
use strict;
use warnings; 
use Text::CSV; 
use Date::Calc qw(:all);
use Date::Calendar;

#-------------------------------------------
#
#-------------------------------------------


#---------------------------------------
# Edit Custom Variables Below
#---------------------------------------

#--------------------------------
# Input and Output files
#--------------------------------

#IN is the directory/filename.csv that the input csv is being written 
+to
#OUT is the directory/filename.csv that the output csv
#my $in = '/opt/data/input.csv';
#my $out = '/opt/data/output.csv';
my $in = 'c:/Users/cottenr/test.csv';
my $out = 'c:/Users/cottenr/foo.csv';


my $employee_number;
my $employee_id;
my $start_date;
my $end_date;
my $total_days;

 
#---------------------------------------
# Program and Loop Section
#---------------------------------------

#Looking for csv file to be written
#START:
while ( -e $in){ 

 my $csv = Text::CSV->new ({  
    binary    => 1,  
    auto_diag => 1,  
    sep_char  => ','    # not really needed as this is the default
}); 

#Open IN and OUT files
open(my $data, '<:encoding(utf8)', $in) or die "Could not open '$in' f
+ile \n";
<$data>;  #skips header line of .csv file
open(OUT, '>encoding(utf8)', $out) or die "Could not open '$out' file\
+n";


#Loop through the file and process data
while (my $fields = $csv->getline( $data )) {  
    $employee_number = $fields->[2];
    $employee_id = $fields->[4];
    $start_date = $fields->[17];
    $end_date = $fields->[18];
    $total_days = $fields->[21];
    
#Conditional eval for Date Comparison
if($end_date > $highnumber) {

} elsif ($count > $mednumber) {

} else {
      
  }


#Layout the format and write to OUT file
print OUT "$employee_number, $employee_id, $start_date, $end_date, $to
+tal_days\n";

} #While loop
if (not $csv->eof) {  
    $csv->error_diag();
}

#---------------------------------------
# Finish it up!
#---------------------------------------

#Print out the footer

#Close the IN and OUT files
close $data;
close $in;
close OUT;
#Delete the INPUT csv file
#unlink $in;
}

#---------------------------------------
# Take a Nap and do it all over again
#---------------------------------------

#sleep 60;
#goto START;

Not sure how to go about pulling the first line of the Array and then searching for other matches to see if the Start date -2days of line 2 is the enddate of line 1. If it matches then add total time and update my enddate on the OUT.

Thanks, Ray

[hdb]

Hope this is helpful!

use strict; use warnings;
use Date::Calendar::Profiles qw( $Profiles );
use Date::Calendar;

sub to_date {
        my ($d,$m,$y) = split /\//, shift;
        return [$y,$m,$d];
}

sub from_date {
        my $date = shift;
        $date =~ s/(\d{4})(\d{2})(\d{2})/$3\/$2\/$1/;
        return $date;
}

my $calendar  = Date::Calendar->new( $Profiles->{'DE-SN'} );

# first read in the whole file and
# record start and end dates for each employee
my %dates;

# simplified data reading, pls stick to Text::CSV
while (<DATA>) {
        next if /^empl/;
        chomp;
        my( $employee_id,$start_date,$end_date,$total_days) = split /,
+/;
        $dates{$employee_id}{$start_date}{'end'} = $end_date;
        $dates{$employee_id}{$start_date}{'days'} = $total_days;
}

# now we have all the data
# and can process it
for my $employid (sort keys %dates) {
        my $data = $dates{$employid}; 
        for my $startdate (keys %{$data}) {
                my $enddate = $$data{$startdate}{'end'};
                next if $enddate eq 'invalid'; # redundant, see below
                # find next businessday
                my $next = from_date( $calendar->add_delta_workdays(to
+_date($enddate),1) );
                # do we have a record for the next business day ?
                if( exists $$data{$next} ) {
                        # merge two records
                        $$data{$startdate}{'end'} = $$data{$next}{'end
+'};
                        $$data{$startdate}{'days'} += $$data{$next}{'d
+ays'};
                        $$data{$next}{'end'} = 'invalid'; # mark redun
+dant
                }
        }
}

for my $employid (sort keys %dates) {
        my $data = $dates{$employid}; 
        for my $startdate  (keys %{$data}) {
                my $enddate = $$data{$startdate}{'end'};
                next if $enddate eq 'invalid';            
                print "$employid,$startdate,$enddate,";
                print $$data{$startdate}{'days'},"\n";
        }
}
__DATA__
employid, start, stop, total
1234,01/04/2013,05/04/2013,5
1234,08/04/2013,12/04/2013,5
1234,18/04/2013,18/04/2013,1
1234,22/04/2013,22/04/2013,1
1234,23/04/2013,23/04/2013,1
5678,01/01/2013,01/01/2013,1

[Anonymous Monk]

Suggestions on modules and how to proceed?

What I would do? If you got the CSV::Text/looping going, then perlintro explains the matching, and sure, Date::Calc can help you generate a list of things to match, DateTime can too, does that work for you?

Hi, I'm lil'the-great-sarcasmo, here are my jokes

... CSV::Text ...

No such module :)

I'm just not sure the best way to look through file for a matching employid and then take into account weekends and holidays. Suggestions on modules and how to proceed?

Simple, if you don't know the best way, pick any way you know how :) and then do it; Then if you want to know a better way say "hello everybody, whats a better way than this?"

Was seeing a lot of talk about Date::Calc..

And then what happened?