Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

How to use 2 files for calculating charges

by rruser (Acolyte)
on Aug 13, 2013 at 20:20 UTC ( #1049334=perlquestion: print w/ replies, xml ) Need Help??
rruser has asked for the wisdom of the Perl Monks concerning the following question:

I have the following situation: a script that calculates charges based on days (subtracting 3 free days) there are occurrences where the 3 days may span more than 1 month I have two files the days spanning to the next month, the current month file I need to be able to take the data from the file that has spanned from the previous month to the current month and overwrite the free days in the current month calculations

file1 (days from previous month) BIGCO-C CMO 15153 L 6/30/13 2 BIGCO-C MKT 575 L 6/30/13 2 BIGCO-C MP 706776 L 6/30/13 2 BIGCO-C MP 718037 L 6/30/13 2 BIGCO-C MP 718071 L 6/30/13 2 MEDCO-C UP 89269 L 6/30/13 2 MEDCO-C UP 89800 L 6/30/13 2 MEDCO-C UP 93068 L 6/30/13 2 PURRCO-C UP 89778 L 6/30/13 2 WILYCO-C CMO 15034 L 6/30/13 2 WILYCO-C MKT 546 L 6/30/13 2 WILYCO-C MP 705339 L 6/30/13 1 WILYCO-C MP 705693 L 6/30/13 2

(the end column number is the days that have already been used) so the number needs to be subtracted by 3 and and excluded from the charges in my script just don't know how to make it happen

file2 (current month - small sample) BIGCO CNW 180304 L 2013,07,16 2013,07,22 BIGCO UP 218226 L 2013,07,20 2013,07,22 BIGCO UP 219144 L 2013,07,23 2013,07,25 BIGCO-C CMO 15032 L 2013,07,01 2013,07,06 BIGCO-C CMO 15076 L 2013,07,01 2013,07,12 BIGCO-C CMO 15128 L 2013,07,01 2013,07,09 BIGCO-C CMO 15153 L 2013,07,01 2013,07,02 BIGCO-C MKT 575 L 2013,07,01 2013,07,02 BIGCO-C MP 705622 L 2013,07,01 2013,07,04 BIGCO-C MP 718037 L 2013,07,01 2013,07,22 BIGCO-C MP 718071 L 2013,07,01 2013,07,17 BIGCO-C UP 95233 L 2013,07,01 2013,07,20 MEDCO CHTT 721530 L 2013,07,16 2013,07,17 MEDCO CHTT 721679 L 2013,07,17 2013,07,18 MEDCO-C UP 88876 E 2013,07,01 2013,07,01 MEDCO-C UP 89269 L 2013,07,01 2013,07,02 MEDCO-C UP 89800 L 2013,07,01 2013,07,02 MEDCO-C UP 93068 L 2013,07,01 2013,07,02 PURRCO UP 95544 L 2013,07,12 2013,07,20 PURRCO-C CTRN 100318 L 2013,07,01 2013,07,15 PURRCO-C UP 89778 L 2013,07,01 2013,07,03 BIGGYCO UP 95664 L 2013,07,05 2013,07,09 BIGGYCO UP 221706 L 2013,07,05 2013,07,09 BIGGYCO-C CMO 20814 L 2013,07,01 2013,07,02 WILYCO CNW 437100 L 2013,07,26 2013,07,30 WILYCO CNW 437106 L 2013,07,08 WILYCO MKT 4399 L 2013,07,03 WILYCO MKT 4559 L 2013,07,23 2013,07,26 WILYCO MP 705320 L 2013,07,23 2013,07,25 WILYCO MP 705860 L 2013,07,26 WILYCO MP 706800 L 2013,07,26 2013,07,31 WILYCO MP 706827 L 2013,07,26 WILYCO MP 706974 L 2013,07,16 2013,07,18 WILYCO UP 94038 L 2013,07,26 WILYCO UP 95048 L 2013,07,23 2013,07,26 WILYCO UP 95050 L 2013,07,08 WILYCO-C CMO 15034 L 2013,07,01 2013,07,11 WILYCO-C MKT 514 L 2013,07,01 2013,07,02 WILYCO-C MKT 546 L 2013,07,01 2013,07,11 WILYCO-C MP 705339 L 2013,07,01 2013,07,12 WILYCO-C MP 705693 L 2013,07,01 2013,07,12

script to calculate the current month charges using file2 generating file3

#!/usr/bin/perl # use strict; use warnings; use Locale::Currency::Format; #use Date::Calc qw( Delta_Days Today Days_in_Month ); use Date::Calc qw( Delta_Days Today Days_in_Month Add_Delta_Days); my $cnt = 0; my $amt; my $mainDir = '/Users/xxx/Scripts/BigRR/RRMarks'; my $filename = '$mainDir\test.txt'; open (my $file, '<', (@ARGV)) or die $!; open OUT, ">", $filename or die "$filename : $!\n"; # work out end of current month my ($y,$m,$d) = Today(); my $mth_end = sprintf "%04d,%02d,%02d",$y,$m,Days_in_Month($y,$m); # ($year,$month,$day) = Add_Delta_Days($year,$month,$day,$Dd); my $prev_mth_end = sprintf "%04d,%02d,%02d", Add_Delta_Days($y,$m,1,-1); printf OUT " Placement Release + Tot Chg\n"; printf OUT " Date Date + Days Days Cost\n"; while (<$file>) { my @dates = split '\s+', $_; $cnt++; # fill empty dates with month end my @ymd1 = split ',',$dates[4] //= $prev_mth_end; my @ymd2 = split ',',$dates[5] //= $prev_mth_end; my $diff = Delta_Days(@ymd1, @ymd2); my $free = ($diff - 3); if ($free >= 0) { my $amt = $free; my $cost = ($amt * 100); my $pfmt = "%-14s %-5s %-8s %-3s %-12s %-12s %-6s %-6s %-6s\n"; printf OUT $pfmt, $dates[0], $dates[1], $dates[2], $dates[3], fmt +_mdy($dates[4]), fmt_mdy($dates[5]), $diff, $amt, fmt_curr($cost); } elsif ($free < 0) { my $amt = 0; my $cost = ($amt * 100); my $pfmt = "%-14s %-5s %-8s %-3s %-12s %-12s %-6s %-6s %-6s\n"; printf OUT $pfmt, $dates[0], $dates[1], $dates[2], $dates[3], fmt_ +mdy($dates[4]), fmt_mdy($dates[5]), $diff, $amt, fmt_curr($cost); } # format currency sub fmt_curr { my $num = shift; my $rounded = $num; if ($num =~ m|\.|) { $rounded = sprintf("%.2f", $num); #round to 2 decimal places } # add comma's while ($rounded =~ s/^(-?\d+)(\d\d\d)/$1,$2/) {} return ('$' .$rounded); } # change yyyy,mm,dd to mm-dd-yy sub fmt_mdy { my $ymd = shift; $ymd =~ s/ //g; # return ' 'x10 if $ymd eq $NULL_DATE; my ($y,$m,$d) = split /\D/,$ymd; if ($y < 99){ $y += 2000 }; return sprintf "%04d-%02d-%02d",$y,$m,$d; } } print $cnt, "\n";

file3 - sample output that is created:

Placement Release Tot Chg Date Date Days Days Cost WILYCO-C BNSF 408033 L 2013-07-01 2013-07-03 2 0 $0 MEDCO CHTT 720576 L 2013-07-05 2013-07-09 4 1 $100 MEDCO CHTT 721283 L 2013-07-05 2013-07-09 4 1 $100 MEDCO CHTT 721307 L 2013-07-16 2013-07-17 1 0 $0 BIGGCO CHTT 721366 L 2013-07-22 2013-07-30 8 5 $500 BIGGCO CHTT 721369 L 2013-07-12 2013-07-15 3 0 $0 BIGGCO CHTT 721398 L 2013-07-17 2013-07-18 1 0 $0 BIGGCO CHTT 721469 L 2013-07-12 2013-07-15 3 0 $0 MEDCO CHTT 721530 L 2013-07-16 2013-07-17 1 0 $0 MEDCO CHTT 721679 L 2013-07-17 2013-07-18 1 0 $0 BIGGCO CHTT 721743 L 2013-07-05 2013-07-08 3 0 $0 PURRCO CMO 13611 L 2013-07-16 2013-07-27 1 8 $800

Comment on How to use 2 files for calculating charges
Select or Download Code
Re: How to use 2 files for calculating charges
by mtmcc (Hermit) on Aug 13, 2013 at 21:15 UTC
    It would make it easier for people to understand your question if you change the <code> tags to surround actual code, and have your question as plain text.
Re: How to use 2 files for calculating charges
by marinersk (Chaplain) on Aug 13, 2013 at 21:17 UTC
    Yeah, I'm trying to convert your description into an algorithm and I keep losing track of what your words mean.

    I probably won't be the first one to get there, but I'm downloading and trying to figure out what you're trying to do.

    :-)

Re: How to use 2 files for calculating charges
by marinersk (Chaplain) on Aug 13, 2013 at 21:31 UTC
    This one may be over my head:
    C:\Steve\Dev\PerlMonks\P-2013-08-13@1518-Time-Days-Discount-Computatio +n>doit1.pl Possible unintended interpolation of @ymd2 in string at C:\Steve\Dev\P +erlMonks\P-2013-08-13@1518-Time-Days-Discount-Computation\doit1.pl li +ne 33. Line 33: my @ymd1 = split ',',$dates[4] //= $prev_mth_end; Execution of C:\Steve\Dev\PerlMonks\P-2013-08-13@1518-Time-Days-Discou +nt-Computation\doit1.pl aborted due to compilation errors.
    So my question is, what the heck does  //=  do?

      So my question is, what the heck does //= do?

      This is the relatively new "defined or" operator

      $a //= $b;

      means that $a is unchanged if it is defined, and is assigned to $b if not defined.

      At least, that's my understanding, I have never used it, but the following test under the Perl debugger seems to confirm it:

      $ perl -de 42 Loading DB routines from perl5db.pl version 1.33 Editor support available. Enter h or `h h' for help, or `man perldebug' for more help. main::(-e:1): 42 DB<1> $a //=5; DB<2> p $a 5 DB<3> $a //=6; DB<4> p $a 5 DB<5>
        This feature added in perl v5.10.0 Refer perl5100delta:
        and the statement $c //= $d; can now be used instead of $c = $d unless defined $c;
        Bill
Re: How to use 2 files for calculating charges
by poj (Priest) on Aug 13, 2013 at 21:40 UTC
    Assuming you can match the records using the number in col 3 then read the values from the previous month into a hash
    open PREV,'<','prev.txt' or die "$!"; my %prev_days=(); #BIGCO-C CMO 180304 L 6/30/13 2 while (<PREV>){ chomp; my @f = split /\s+/,$_; $prev_days{$f[2]} = $f[5]; }
    and then add them into your $amt calc
    while (<$file>) { my @f = split '\s+', $_; $cnt++; # fill empty dates with month end my @ymd1 = split ',',$f[4] //= $prev_mth_end; my @ymd2 = split ',',$f[5] //= $prev_mth_end; my $diff = Delta_Days(@ymd1, @ymd2); my $prev = $prev_days{$f[2]} //= 0; my $amt = ($diff + $prev > 3) ? $diff + $prev - 3 : 0; my $cost = ($amt * 100); my $pfmt = "%-14s %-5s %-8s %-3s %-12s %-12s %-6s %-6s %-6s %-6s\n"; printf OUT $pfmt, @f[0..3], fmt_mdy($f[4]), fmt_mdy($f[5]), $diff, $ +amt, fmt_curr($cost), $prev; }
    poj
Re: How to use 2 files for calculating charges
by Laurent_R (Parson) on Aug 13, 2013 at 22:37 UTC

    Your code is incredibly long for something that looks pretty simple, if I understand it correctly.

    You haven't described your files, but I assume that the three first columns, put together, define a unique identifier for the tasks you want to process.

    You first need to read the first file and store in a hash the 3-day gratuity left-overs. Something like this (untested):

    my %free_days_left; while (<$file1>) { chomp; my @fields = split /\s+/, $_; my $key = join "-", @fields[0..2]; $free_days_left{$key} = $fields[5]; }

    This could be made more compact, but I prefer just to explain the process as clearly as possible.

    Now, the %free_days_left hash contains the free days left. You just need to read through the second file, compute the number of days, subtract the free days and output the result (again, a general idea, not tested).

    while (<$file2>) { chomp; my @fields = split /\s+/, $_; my $key = join "-", @fields[0..2]; my ($start_date, $end_date) = @fields[5,6]; my $start_day = (split /,/, $start_date)[2]; my $end_day = (split /,/, $end_date)[2]; my $duration = $end_day - $start_day + 1: my $corrected_duration = exists $free_days_left{$key}? $duration +- $free_days_left{$key} : $duration; print $file3 join " ", $_, $duration, $corrected_duration, $corre +cted_duration * $unit_price, "\n"; }

    OK, the formating of the output file may need a bit more work, but I guess you've got the basic idea.

Re: How to use 2 files for calculating charges
by jwkrahn (Monsignor) on Aug 14, 2013 at 04:45 UTC
    my $cnt = 0; ... $cnt++; ... print $cnt, "\n";

    You don't really need the $cnt variable.    Just remove the first two lines and change the last line to:

    print "$.\n";


    my $filename = '$mainDir\test.txt';

    Variables don't interpolate in single quoted strings so you should change that to a double quoted string:

    my $filename = "$mainDir\test.txt";


    my $mth_end = sprintf "%04d,%02d,%02d",$y,$m,Days_in_Month($y,$m);

    You never use $mth_end anywhere.



    printf OUT " Placement Release + Tot Chg\n"; printf OUT " Date Date + Days Days Cost\n";

    You should be using print instead of printf:

    print OUT " Placement Release + Tot Chg\n"; print OUT " Date Date + Days Days Cost\n";


    my @dates = split '\s+', $_;

    Or just:

    my @dates = split;


    if ($free >= 0) { my $amt = $free; my $cost = ($amt * 100); my $pfmt = "%-14s %-5s %-8s %-3s %-12s %-12s %-6s %-6s %-6s\n"; printf OUT $pfmt, $dates[0], $dates[1], $dates[2], $dates[3], fmt +_mdy($dates[4]), fmt_mdy($dates[5]), $diff, $amt, fmt_curr($cost); } elsif ($free < 0) { my $amt = 0; my $cost = ($amt * 100); my $pfmt = "%-14s %-5s %-8s %-3s %-12s %-12s %-6s %-6s %-6s\n"; printf OUT $pfmt, $dates[0], $dates[1], $dates[2], $dates[3], fmt_ +mdy($dates[4]), fmt_mdy($dates[5]), $diff, $amt, fmt_curr($cost); }

    You have a lot of code duplication in there.    You could simplify that as:

    my $amt = $free < 0 ? 0 : $free; my $cost = $amt * 100; my $pfmt = "%-14s %-5s %-8s %-3s %-12s %-12s %-6s %-6s %-6s\n"; printf OUT $pfmt, @dates[ 0, 1, 2, 3 ], fmt_mdy( $dates[ 4 ] ), fmt_ +mdy( $dates[ 5 ] ), $diff, $amt, fmt_curr( $cost );


    $ymd =~ s/ //g; # return ' 'x10 if $ymd eq $NULL_DATE; my ($y,$m,$d) = split /\D/,$ymd;

    Why remove spaces when they match /\D/ which your split will remove anyways?    The problem is that split /\D/,$ymd will only remove single non-digit characters and you need to remove multiple non-digit characters:

    my ( $y, $m, $d ) = split /\D+/, $ymd;

    Or just capture the digit characters:

    my ( $y, $m, $d ) = $ymd =~ /\d+/g;


    while (<$file>) { ... sub fmt_curr { ... sub fmt_mdy { ... }

    Subroutine names are available globally so putting them inside a while loop does not effect their scope, but it might complicate the use of variables inside the subroutine.

Re: How to use 2 files for calculating charges
by Cristoforo (Deacon) on Aug 15, 2013 at 02:44 UTC
    Some questions about your solution and data. Some data from file2:
    WILYCO CNW 437106 L 2013,07,08 WILYCO MKT 4399 L 2013,07,03 WILYCO MKT 4559 L 2013,07,23 2013,07,26
    What do you want to do when there is not a second date? Also, is it possible to not have a first date? What would you do in that case? Because, your code is (date calculations flawed I think - see next question about the dates):
    my @ymd1 = split ',',$dates[4] //= $prev_mth_end; my @ymd2 = split ',',$dates[5] //= $prev_mth_end;
    Do you really want to assign $prev_mth_end to both if both are blank?
    # work out end of current month my ($y,$m,$d) = Today(); my $mth_end = sprintf "%04d,%02d,%02d",$y,$m,Days_in_Month($y,$m); # ($year,$month,$day) = Add_Delta_Days($year,$month,$day,$Dd); my $prev_mth_end = sprintf "%04d,%02d,%02d", Add_Delta_Days($y,$m,1,-1);
    It is August now and your date code is using Today(), but your file 2 data is all for the month of July. I think this might be a problem, as the dates won't pertain to July but instead August.

    I don't see where you are taking into account for the days from the previous month (June, file 1).

    For example, a record from file 1 shows 2 days:

    BIGCO-C  MKT   575 L 6/30/13  2
    The corresponding record from file 2 shows:
    BIGCO-C   MKT      575 L 2013,07,01 2013,07,02
    Subtracting 2013,07,01 from 2013,07,02 = 1 day? Then, total days be 2 + 1 = 3 in this case?

    From that, subtracting the 3 free days would give 0 billing days? Are you calculating Demurrage?

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1049334]
Approved by davido
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (6)
As of 2014-08-29 06:29 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (275 votes), past polls