package RPS::Sale::Report;

use strict;

#use warnings;

use Spreadsheet::WriteExcel;
use Spreadsheet::WriteExcel::Big;

use lib '/app/tools/common/lib';
use Common::Util;
use Common::RSMath;
use Common::Locale;
use Common::Client;
use Common::Locale;
use Common::Log;
use Common::Email;

use lib '/app/tools/rps/lib';
use lib '/app/tools/data_classes/lib';
use lib '/app/tools/raptor/lib';
use Raptor::DB::Item::File;
use Raptor::DB::Item::Sale;
use Client::Client;
use Client::Service;
use RPS::File::File;
use RPS::File::Sale;
use Product::ProductAlbum;
use Product::ProductTrack;
use Period::RoyaltyPeriod;

use RPS::Sale::StatRates;

use constant WMG             => 21;
use constant REPORT_BASE_DIR => '/app/shared/sale_report';

my %client_info = ();
my %report_type = (
    royalty => {
        name    => 'RoyaltyReport',
        ext     => 'txt',
        ext_WMG => 'zip',
    },
    royalty_excel => {
        name => 'RoyaltyReport',
        ext  => 'xls',
    },
    royalty_xml => {
        name => 'RoyaltyReport',
        ext  => 'xml',
    },
    royalty_consolidation => {
        name => 'RoyaltyReport_Consolidation',
        ext  => 'tsv',
    },
    accounting => {
        name => 'RoyaltyReport_CUSTOM1',
        ext  => 'csv',
    },
    reconciliation => {
        name => 'RoyaltyReport_Reconciliation',
        ext  => 'xls',
    },
    trackerpage => {
        name => 'TrackerSummary',
        ext  => 'xls',
    },
    billing => {
        name => 'BillingReport',
        ext  => 'txt',
    },
);

my %field_map = (
    'label-name'     => 'LabelName',
    'service-id'     => 'ServiceID',
    'service-name'   => 'ServiceName',
    'territory'      => 'CountryCode',       # 2 char country code (eg. US)
    'period-begin'   => 'DateBegin',
    'period-end'     => 'DateEnd',
    'product-type'   => 'ProductType',       # (a)lbumm (t)rack
    'product-format' => 'FormatType',        # (d)ownload, (s)tream, (r)ingtone
    'catalog-id'     => 'CatalogID',
    'upc'            => 'UPC',
    'album-id'       => 'ClientAlbumID',
    'album-name'     => 'AlbumName',
    'album-artist'   => 'AlbumArtistName',
    'release-date'   => 'ReleaseDate',
    'upc-alt'        => 'UPCAlt',
    'album-custom-1' => 'AlbumCustom1',
    'album-custom-2' => 'AlbumCustom2',
    'album-custom-3' => 'AlbumCustom3',
    'isrc'           => 'ISRC',
    'track-id'       => 'ClientTrackID',
    'disc-no'        => 'DiscNumber',
    'track-no'       => 'TrackNum',
    'track-name'     => 'TrackName',
    'track-artist'   => 'ArtistName',
    'track-custom-1' => 'TrackCustom1',
    'track-custom-2' => 'TrackCustom2',
    'track-custom-3' => 'TrackCustom3',
    'units'          => 'Units',
    'unit-price'     => 'Price',
    'ext-price'      => 'TotalPrice',        # price * units
    'currency-code'  => 'CurrencyCode',      # 3 char currency code (eg. USD)
    'currency-conv'  => 'ConversionRate',
    'usd-unit-price' => 'USDPrice',          # price * conversion rate
    'usd-ext-price'  => 'USDTotalPrice',     # price * units * conversion rate
    'free'           => 'Free',              # (y)es (n)o
    'dist-fee'       => 'DistFeePct',
    'usd-net-price'  => 'USDNetPrice',
    'distributor'    => 'Distributor',
    'media-type'     => 'MediaType',
);

# to preserve order
my @fields = (

    # new fields need to be added on the fly to keep things consistent for
    # existing customers (e.g. Instinct), so don't add anything further to the
    # fields array.
    #
    # probably need to come up with something a little better down the road...
    'label-name',        # LabelName
    'service-id',        # ServiceID
    'service-name',      # ServiceName
    'distributor',       # Distributor
    'territory',         # CountryCode
    'period-begin',      # DateBegin
    'period-end',        # DateEnd
    'product-type',      # ProductType
    'product-format',    # FormatType
    'catalog-id',        # CatalogID
    'upc',               # UPC
    'album-id',          # ClientAlbumID
    'album-name',        # AlbumName
    'album-artist',      # AlbumArtistName
    'release-date',      # ReleaseDate
    'upc-alt',           # UPCAlt
    'album-custom-1',    # AlbumCustom1
    'album-custom-2',    # AlbumCustom2
    'album-custom-3',    # AlbumCustom3
    'isrc',              # ISRC
    'track-id',          # ClientTrackID (client_track_id)
    'disc-no',           # DiscNumber
    'track-no',          # TrackNum
    'track-name',        # TrackName
    'track-artist',      # TrackArtist
    'track-custom-1',    # TrackCustom1
    'track-custom-2',    # TrackCustom2
    'track-custom-3',    # TrackCustom3
    'units',             # Units
    'unit-price',        # Price
    'ext-price',         # TotalPrice
    'currency-code',     # CurrencyCode
    'currency-conv',     # ConversionRate
    'usd-unit-price',    # USDPrice
    'usd-ext-price',     # USDTotalPrice
    'free'               # Free
                         # really, no new fields in this array. see comments above.
);

# WMG
sub CreateRoyaltyReportWMG {
    my $class = shift;
    my %args  = @_;

    my @SPECIAL_TYPES = qw(aod tod UNIV);

    my $period    = $args{period_id};
    my $client_id = $args{client_id};
    my $rev       = $args{rev} || '01';
    my $file_id   = $args{file_id};
    unless ( $period && $client_id && $file_id ) {
        print STDERR "missing req'd args - period: $period - client: $client_id - file: $file_id\n";
        return undef;
    }

    my $client = _getClientInfo($client_id);

    my $dest_dir = join( '/', REPORT_BASE_DIR, $client->{clean_name}, 'period_' . $period );
    if ( !-d $dest_dir && !mkdir($dest_dir) ) {
        print STDERR "can't create dir: $dest_dir\n";
        return undef;
    }

    my $tmp_dir = REPORT_BASE_DIR . '/tmp';
    if ( !-d $tmp_dir && !mkdir($tmp_dir) ) {
        print STDERR "can't create tmp: $tmp_dir\n";
        return undef;
    }

    my $sales = RPS::File::Sales->new( client_id => $client_id );
    $sales->GetFinalSalesByPeriod( period_id => $period, file_id => $file_id );

    my $fileObj = RPS::File::File->new( file_id => $file_id, client_id => $client_id );

    #return "period mismatch" unless ($period eq $fileObj->PeriodID());

    my $service_id = $fileObj->ServiceID();
    my $orig_name  = $fileObj->OrigFileName();
    my $svc_name   = Client::Service->new( client_id => $client_id, service_id => $service_id )->ServiceName();
    $svc_name =~ s/\W+//g;

    # TODO: need to get svc_name from a function call

    my $special_types = join( '|', @SPECIAL_TYPES );

    # build file name MusicMatch_02012005_02282005_01.tab
    my @dates = $sales->GetDateRangeByFileID($file_id);
    _reformat_dates( \@dates );
    my $file_name = join( '_', $svc_name, $dates[0], $dates[1], $rev );
    $file_name .= "_$1" if ( $orig_name =~ m/($special_types)/ );

    my $dest_file_path = "$dest_dir/$file_name.tab";
    my $fc             = 1;
    while ( -f $dest_file_path ) {
        $dest_file_path = sprintf( "%s/%s_%d.tab", $dest_dir, $file_name, $fc++ );
        last if ( $fc > 10 );    # just to be safe
    }
    print STDERR "dest: $dest_file_path\n";
    return $dest_file_path if ( -f $dest_file_path && -s $dest_file_path );

    # let's use a unique name for the tmp file
    my $tmp_file_path = "$tmp_dir/$client->{clean_name}_$file_id.txt.$$";
    unless ( open TMP, ">$tmp_file_path" ) {
        print STDERR "can't create file $tmp_file_path\n";
        return undef;
    }

    require Import::WMG::Util;

    my @output_fields = qw(SvcProdID TrackName BLANK AlbumName ArtistName UPC ISRC
      USDPrice Units USDTotalPrice IncomeType LabelName Minutes Seconds ServiceName);
    while ( ( my $sale = $sales->GetNext() ) ) {
        my $href = $sale->GetObjectXML();

        ## skip phys sales
        next if ( $sale->ProductType eq '2' );

        # Decide here whether to include sales of 'Free' tracks
        # in this report. Don't skip them if either the client
        # wants them or they have a price

        if ( $href->{Free} && !$client->{report_free} ) {
            next unless ( $href->{Price} );    ## price is NULL
            next if ( $href->{Price} == 0 );   ## price is 0.0000
        }

        $href->{ServiceName} = uc $svc_name;
        my $product;
        if ( $sale->ProductType eq 'T' ) {
            $product = new Product::ProductTrack( product_id => $sale->ProductID, client_id => $client_id );

            # set some values
            $href->{TrackName} = $product->TrackName;

            #$href->{AlbumArtistName} = $product->AlbumArtistName;
            $href->{ArtistName}    = $product->ArtistName;
            $href->{ClientTrackID} = $product->ClientTrackID;
            $href->{UPC}           = $product->UPC || $product->ISRC;
            $href->{ISRC}          = $product->ISRC;
            $href->{TrackNum}      = $product->TrackNumber;
            $href->{TrackCustom1}  = $product->TrackCustom1;
            $href->{TrackCustom2}  = $product->TrackCustom2;
            $href->{TrackCustom3}  = $product->TrackCustom3;
            $href->{Duration}      = $product->Duration;
            $href->{SvcProdID}     = $href->{ServiceProductID} || $product->ClientTrackID || $product->ISRC;
        } elsif ( $sale->ProductType eq 'A' ) {
            $product = new Product::ProductAlbum( product_id => $sale->ProductID, client_id => $client_id );

            # set some values
            $href->{ArtistName} = $product->ArtistName;
            $href->{UPC}        = $product->UPC;
            $href->{SvcProdID}  = $href->{ServiceProductID} || $product->ClientAlbumID || $product->UPC;
        }

        # for the purpose of sorting
        $href->{ProductType} = $sale->ProductType;

        $href->{AlbumName}         = $product->AlbumName;
        $href->{ClientAlbumID}     = $product->ClientAlbumID;
        $href->{DiscNumber}        = $product->DiscNumber;
        $href->{CatalogID}         = $product->CatalogID;
        $href->{ReleaseDate}       = $product->ReleaseDate;
        $href->{ReleaseDatePretty} = Common::Client::Current()->Locale()->formatDate( $product->ReleaseDate );
        $href->{UPCAlt}            = $product->UPCAlt;
        $href->{AlbumCustom1}      = $product->AlbumCustom1;
        $href->{AlbumCustom2}      = $product->AlbumCustom2;
        $href->{AlbumCustom3}      = $product->AlbumCustom3;
        $href->{LabelName}         = $product->LabelName;
        $href->{LabelName}         = "Unknown" if ( $product->LabelName eq "" );
        $href->{Units}             = sprintf( "%09d", $href->{Units} );
        $href->{Price}             = sprintf( "%09.8f", $href->{Price} );
        $href->{TotalPrice}        = sprintf( "%012.8f", $href->{Units} * $href->{Price} );
        $href->{USDPrice}          = sprintf( "%09.8f", $href->{Price} * $href->{ConversionRate} );
        $href->{USDTotalPrice}     = sprintf( "%012.8f", $href->{Units} * $href->{USDPrice} );
        $href->{Free}              = $href->{Free} ? 'Y' : 'N';

        $href->{IncomeType} = Import::WMG::Util::getWiredFormatName( $href->{FormatType} );

        $href->{BLANK}   = '';
        $href->{Minutes} = sprintf( "%03d", $href->{Duration} / 60 );
        $href->{Seconds} = sprintf( "%04d", $href->{Duration} - ( $href->{Minutes} * 60 ) );

        print TMP join( "\t", @$href{@output_fields} );
        print TMP "\r\n";
    }
    close TMP;

    # do it. do it now.
    rename( $tmp_file_path, $dest_file_path ) || die "CreateRoyaltyReport: can't copy file";

    return $dest_file_path;
}

sub CreateWirelessRoyaltyReportWMG {
    my $class = shift;
    my %args  = @_;

    my $period    = $args{period_id};
    my $client_id = $args{client_id};
    my $file_id   = $args{file_id};
    unless ( $period && $client_id && $file_id ) {
        print STDERR "missing req'd args - period: $period - client: $client_id - file: $file_id\n";
        return undef;
    }

    my $client = _getClientInfo($client_id);

    my $dest_dir = join( '/', REPORT_BASE_DIR, $client->{clean_name}, 'period_' . $period );
    if ( !-d $dest_dir && !mkdir($dest_dir) ) {
        print STDERR "can't create dir: $dest_dir\n";
        return undef;
    }

    my $tmp_dir = REPORT_BASE_DIR . '/tmp';
    if ( !-d $tmp_dir && !mkdir($tmp_dir) ) {
        print STDERR "can't create tmp: $tmp_dir\n";
        return undef;
    }

    my $sales = RPS::File::Sales->new( client_id => $client_id );
    my $sale_count = $sales->GetFinalSalesByPeriod( period_id => $period, file_id => $file_id );
    return 0 unless $sale_count;

    my $fileObj = RPS::File::File->new( file_id => $file_id, client_id => $client_id );

    #return "period mismatch" unless ($period eq $fileObj->PeriodID());

    my $service_id = $fileObj->ServiceID();
    my $orig_name  = $fileObj->OrigFileName();
    my $svc_name   = Client::Service->new( client_id => $client_id, service_id => $service_id )->ServiceName();
    $svc_name =~ s/\W+//g;

    # build file name MusicMatch_2006-02-01_2006-02-28.txt
    my @dates = $sales->GetDateRangeByFileID($file_id);
    my ($mq) = _determine_sale_date(@dates);
    $mq =~ s/^(.).*/$1/;
    my $file_name = join( '_', $svc_name, $mq, $dates[0], $dates[1] );
    map {
        s/^(\d+)-(.+)$/$2$1/;
        s/\D//g;
    } @dates;

    my $dest_file_path = "$dest_dir/$file_name.txt";
    my $fc             = 1;
    while ( -f $dest_file_path ) {
        $dest_file_path = sprintf( "%s/%s_%d.txt", $dest_dir, $file_name, $fc++ );
        last if ( $fc > 10 );    # just to be safe
    }
    print STDERR "dest: $dest_file_path\n";
    return $dest_file_path if ( -f $dest_file_path && -s $dest_file_path );

    # let's use a unique name for the tmp file
    my $tmp_file_path = "$tmp_dir/$client->{clean_name}_$file_id.txt.$$";
    unless ( open TMP, ">$tmp_file_path" ) {
        print STDERR "can't create file $tmp_file_path\n";
        return undef;
    }

    my $now = Common::Util::today();
    $now =~ s/^(\d+)-(.+)$/$2$1/;
    $now =~ s/\D//g;
    print TMP join( '|', 'HDR', 0, @dates, $now, $mq, $fileObj->Units, sprintf( "%.8f", $fileObj->Revenue ), $sale_count );
    print TMP "\n";

    my @wireless_fields =
      qw(BillToNum BillToName SellToNum SellToName SaleDate AcctBasis ArtistName LabelName TrackName Units Retail SaleTotal RemitAmt WholesaleRate Price FormatName PayoutType ISRC Grid PriceType Matched);

    require Import::WMG::Util;
    my ( $billto_num, $billto_name ) = Import::WMG::Util::getWirelessBillto($service_id);
    unless ($billto_num) {
        print STDERR "no billto mapping for $service_id\n";
        return undef;
    }

    my $revenue = 0;
    while ( ( my $sale = $sales->GetNext() ) ) {
        my $href = $sale->GetObjectXML();

        ## skip phys sales
        next if ( $sale->ProductType eq '2' );

        # Decide here whether to include sales of 'Free' tracks
        # in this report. Don't skip them if either the client
        # wants them or they have a price

        if ( $href->{Free} && !$client->{report_free} ) {
            next unless ( $href->{Price} );    ## price is NULL
            next if ( $href->{Price} == 0 );   ## price is 0.0000
        }

        $href->{ServiceID} ||= $service_id;
        $href->{ServiceName} = Client::Service->new( client_id => $client_id, service_id => $href->{ServiceID} )->ServiceName();
        ( $href->{AcctBasis}, $href->{SaleDate} ) = _determine_sale_date( $href->{DateBegin}, $href->{DateEnd} );

        if ( $sale->ProductID )                # prod_id may not exist in wireless files
        {
            my $product;
            if ( $sale->ProductType eq 'T' ) {
                $product = new Product::ProductTrack( product_id => $sale->ProductID, client_id => $client_id );

                # set some values
                $href->{TrackName} = $product->TrackName;
                $href->{ISRC}      = $product->ISRC;
            } elsif ( $sale->ProductType eq 'A' ) {
                $product = new Product::ProductAlbum( product_id => $sale->ProductID, client_id => $client_id );
                $href->{TrackName} = $product->AlbumName;
            }

            $href->{Grid}       = $product->AlbumCustom1;
            $href->{ArtistName} = $product->ArtistName;

            #$href->{AlbumName} = $product->AlbumName;
            $href->{LabelName}  = $product->LabelName;
            $href->{FormatName} = $product->AlbumCustom2;
            $href->{Matched}    = 'x';
        } else {
            $href->{FormatName} = Import::WMG::Util::getWirelessFormatName( $href->{FormatType} );
            $href->{Matched}    = '';
            $href->{TrackName}  = $sale->AlbumName if ( $sale->ProductType eq 'A' );
        }

        ( $href->{BillToNum}, $href->{BillToName} ) = ( $billto_num, $billto_name );
        ( $href->{SellToNum}, $href->{SellToName} ) = Import::WMG::Util::getWirelessSellto( $href->{ServiceID}, $billto_num );
        unless ( $href->{SellToNum} ) {
            print STDERR "no sellto mapping for $href->{ServiceID}, $billto_num\n";
            return undef;
        }

        $href->{RemitAmt}  = sprintf( "%.8f", $href->{Price} * $href->{Units} );
        $href->{SaleTotal} = sprintf( "%.8f", $href->{Retail} * $href->{Units} );
        $href->{Price}     = sprintf( "%.8f", $href->{Price} );
        $href->{Retail}    = sprintf( "%.8f", $href->{Retail} );

        my $i = 1;
        print TMP 'DTL|';
        print TMP join( '|', @$href{@wireless_fields} );
        print TMP "\n";
    }
    close TMP;

    # do it. do it now.
    rename( $tmp_file_path, $dest_file_path ) || die "CreateRoyaltyReport: can't copy file";

    return $dest_file_path;
}


# STD
sub CreateRoyaltyReport {
    my ($class, %args) = @_;

    Common::Log::Print("In Report.pm");
    my $period         = $args{period_id};
    my $clientID       = $args{client_id};
    my $oClient        = _getClientInfo($clientID) || return;
    my $oLocale        = Common::Client::Current()->Locale();
    my $currencyCodeLC = lc $oLocale->currencyFormat()->currencyCode();
    my $currencySymbol = $oLocale->currencyFormat()->symbol();
    my $services       = Client::Service::GetAllServiceNames( client_id => $clientID );
    my $oSales         = RPS::File::Sales->new( client_id => $clientID );
    $oSales->GetFinalSalesByPeriodExtended( period_id => $period );

    # add extra fields for Instinct
    if ( $clientID == 11 ) {
        push( @fields, 'usd-mechanicals', 'usd-net-ext-price' );
        $field_map{'usd-mechanicals'}   = 'USDMechanicals';
        $field_map{'usd-net-ext-price'} = 'USDNetExtPrice',;
    }

    # all new fields must be added after custom fields for clients (like Instinct).
    # this means this section of code depends pretty heavily on sequence now (at least
    # with regard to adding fields to @fields)
    push( @fields, 'dist-fee', 'usd-net-price', 'media-type', );

    # initialize tmp file
    my $tmpDir = REPORT_BASE_DIR . '/tmp';
    if ( !-d $tmpDir && !mkdir($tmpDir) ) {
        print STDERR "can't create tmp dir: $tmpDir\n";
        return;
    }
    my $tmpFilePath = "$tmpDir/$oClient->{clean_name}.txt.$$";
    unless ( open TMP, '>', $tmpFilePath ) {
        print STDERR "can't create file $tmpFilePath\n";
        return;
    }
    binmode( TMP, ':utf8' );

    # we'll count the header we'll write too
    my $lineCount = 1;
    my ( @error_products, @error_sales, %filesCache );
    while ( my $hRow = $oSales->{sth}->fetchrow_hashref() ) {
        next unless $hRow->{ProductType} =~ /^A|T$/;

        if ( $hRow->{Free} && !$oClient->{report_free} ) {
            next if !$hRow->{Price} || $hRow->{Price} == 0;       # price is NULL || price is 0.0000
        }

        my $fileID = $hRow->{FileID};
        my $oFile  = $filesCache{ "$fileID|$clientID" } ||= RPS::File::File->new( file_id => $fileID, client_id => $clientID );

        if ( $hRow->{ServiceID} ) {
            $hRow->{Distributor} = $services->{ $oFile->ServiceID };
        } else {
            $hRow->{Distributor} = '';
            $hRow->{ServiceID}   = $hRow->{ServiceID};
        }
        $hRow->{ServiceName}     = $services->{ $hRow->{ServiceID} };
        $hRow->{ServiceNameSort} = uc $hRow->{ServiceName};

        if ( $hRow->{ProductType} eq 'T' ) {
            if ( !$hRow->{TrackName} && !$hRow->{ArtistName} && !$hRow->{ProductType} ) {
                push( @error_sales,    $hRow->{SaleID} );
                push( @error_products, $hRow->{ProductID} );
            }

            ## mechanical calculations for instinct
            ## SKIP IF eMUSIC
            if (    $clientID == 11
                && $hRow->{FormatType} eq RPS::File::Sale::FORMAT_DOWNLOAD
                && $hRow->{Price} != 0
                && $hRow->{ServiceID} != 5
            ) {
                $hRow->{USDMechanicals} = RPS::Sale::StatRates::getStatRate( date => $hRow->{DateEnd}, sec => $hRow->{Duration} );
                $hRow->{USDMechanicals} *= $hRow->{Units};
            }

        } elsif ( $hRow->{ProductType} ne "T" ) {
            if ( !$hRow->{ArtistName} && !$hRow->{ProductType} ) {
                push( @error_sales,    $hRow->{SaleID} );
                push( @error_products, $hRow->{ProductID} );
            }

            ## mechanical calculations for instinct
            ## SKIP IF eMUSIC
            if (    $clientID == 11
                && $hRow->{FormatType} eq RPS::File::Sale::FORMAT_DOWNLOAD
                && $hRow->{Price} != 0
                && $hRow->{ServiceID} != 5
            ) {
                $hRow->{USDMechanicals} = 0;
                map { $hRow->{USDMechanicals} += RPS::Sale::StatRates::getStatRate( date => $hRow->{DateEnd}, sec => $_ ) } split( '|', $hRow->{Duration} );
                $hRow->{USDMechanicals} *= $hRow->{Units};
            }
        }

        # fields calculation
        $hRow->{ReleaseDatePretty} = Common::Client::Current()->Locale()->formatDate( $hRow->{ReleaseDate} );
        $hRow->{Price}             = Common::RSMath::round( $hRow->{Price}, 8 );
        $hRow->{TotalPrice}        = $hRow->{Units} * $hRow->{Price};
        $hRow->{ConversionRate}    = Common::RSMath::round( $hRow->{ConversionRate}, 8 );
        $hRow->{USDPrice}          = Common::RSMath::round( ( $hRow->{Price} * $hRow->{ConversionRate} ), 8 );
        $hRow->{USDTotalPrice}     = $hRow->{Units} * $hRow->{USDPrice};
        $hRow->{Free}              = $hRow->{Free} ? 'Y' : 'N';
        $hRow->{LabelName}         ||= "Unknown";

        if ( my $dist_fee = $oFile->GetDistFeePct( $hRow->{FormatType} ) ) {
            $hRow->{DistFeePct}  = $dist_fee * .01;
            $hRow->{USDNetPrice} = $hRow->{USDTotalPrice} * ( ( 100 - $dist_fee ) * .01 );
        } else {
            $hRow->{DistFeePct}  = 0;
            $hRow->{USDNetPrice} = $hRow->{USDTotalPrice};
        }

        if ( $clientID == 11 ) {
            $hRow->{USDNetExtPrice} = $hRow->{USDTotalPrice} - $hRow->{USDMechanicals};
        }

        my @values = ();
        foreach my $fieldName (@fields) {
            my $value = $hRow->{ $field_map{$fieldName} };
            $value =~ s/\"/\"\"/g;
            $value =~ s/\s+/ /g;
            push @values, $value;
        }
        print TMP join( "\t", @values ) . "\n";

        $lineCount++;
    }
    close TMP;

    # create destination file
    open( READ, '<', $tmpFilePath );
    binmode( READ, ':utf8' );

    {
        my $destFilePath = _getReportName(
            type     => 'royalty',
            period   => $period,
            client   => $oClient->{clean_name},
            clientID => $clientID,
        );
        return 1 if ( -f $destFilePath && -s $destFilePath );

        open( WRITE, '>', $destFilePath );
        binmode( WRITE, ':utf8' );
        my $header = join( "\t", @fields );
        $header =~ s/usd/$currencyCodeLC/g;
        print WRITE "$header\n";
        print WRITE $_ while ( <READ> );
        close WRITE;
    }

    close READ;
    unlink ($tmpFilePath);

    #mail out product warning if applicable
    if ( @error_products > 0 ) {
        my $recipient = "rps_error\@royaltyshare.com";

        my $from = 'do-not-reply@royaltyshare.com';
        my $to = $recipient;
        my $subject = $oClient->{clean_name} . " Period: " . $period . " Royalty Report Generation Error";

        my $body = "The following " . $oClient->{clean_name} . " Period " . $period . " Sales/Products are corrupt:\n";
        for ( my $x = 0 ; $x < @error_products ; $x++ ) {
            $body .= "Sale: " . $error_sales[$x] . " Product: " . $error_products[$x] . "\n";
        }

        Common::Email->SendAWS(
            to      => $to,
            from    => $from,
            subject => $subject,
            body    => $body
        );
    }

    return $lineCount;
}


sub report_sort {

    # cheryl knows better so...

         $a->{LabelName} cmp $b->{LabelName}
      or $a->{ServiceNameSort} cmp $b->{ServiceNameSort}
      or $a->{CountryCode} cmp $b->{CountryCode}
      or $a->{DateBegin} <=> $b->{DateBegin}
      or $a->{ProductType} cmp $b->{ProductType}
      or $a->{FormatType} cmp $b->{FormatType};

    #	# by label
    #	$a->{LabelName} cmp $b->{LabelName} or
    #
    #	# by client album id
    #	$a->{ClientAlbumID} <=> $b->{ClientAlbumID} or
    #
    #	# by product type (album or track)
    #	$a->{ProductType} cmp $b->{ProductType}
    #		or
    #
    #	# by disc number
    #	$a->{DiscNumber} <=> $b->{DiscNumber}
    #		or
    #
    #	# by track number
    #	$a->{TrackNum} <=> $b->{TrackNum}
    #		or
    #
    #	# by service
    #	$a->{ServiceID} <=> $b->{ServiceID}
    #		or
    #
    #	# by country
    #	$a->{CountryCode} cmp $b->{CountryCode}
    #		or
    #
    #	# by begin date
    #	$a->{DateBegin} <=> $b->{DateBegin};
}

sub CreateRoyaltyReportExcel {
    my $class   = shift;
    my %args    = @_;
    my $txtFile = $args{src_file} || return undef;

    return undef if ( !-e $txtFile );

    my $client = _getClientInfo( $args{client_id} );

    my $excelFile = _getReportName(
        type     => 'royalty_excel',
        period   => $args{period_id},
        client   => $client->{clean_name},
        clientID => $args{client_id},
    );
    return $excelFile if ( -B $excelFile && -s $excelFile );    # an existing excel file is a binary file

    my $locale         = Common::Client::Current()->Locale();
    my $currencyCode   = $locale->currencyFormat()->currencyCode();
    my $currencySymbol = $locale->currencyFormat()->symbol();

    open INPUT, $txtFile or return undef;                       # die "Couldn't open input file $txtFile: $!";
    binmode( INPUT, ':utf8' );

    # grab the header row before hand,
    # we'll need to keep this around
    my $headerLine = <INPUT>;
    chomp($headerLine);
    my @headerValues = split( /\t/, $headerLine );

    # "find" the units and usd-ext-price column so that we'll still work if it moves
    my ( $unitsIndex, $totalIndex, $netRevenueIndex );
    for ( my $ix = 0 ; $ix < $#headerValues ; $ix++ ) {
        $totalIndex      = $ix if ( lc( $headerValues[$ix] ) eq lc($currencyCode) . '-ext-price' );
        $unitsIndex      = $ix if ( lc( $headerValues[$ix] ) eq 'units' );
        $netRevenueIndex = $ix if ( lc( $headerValues[$ix] ) eq lc($currencyCode) . '-net-price' );
    }

    # create our excel object and reusable formatter
    # each label will get its own worksheet in the spreadsheet.
    my $workbook   = Spreadsheet::WriteExcel::Big->new($excelFile);
    my $formatBold = $workbook->add_format();
    $formatBold->set_bold();

    my ( $worksheet, $count, $labelPageNo, $labelSum, $curLabel, $prevLabel, $excelLabel, %excelTabs );
    my %labelSummary = ();

    # start by adding a summary tab
    my $formatRBold = $workbook->add_format();
    $formatRBold->set_bold();
    $formatRBold->set_align('right');
    my $worksheetSum = $workbook->add_worksheet('Summary');
    $worksheetSum->set_column( 0, 0, 28 );
    $worksheetSum->set_column( 1, 2, 12 );
    $worksheetSum->write( 0, 0, 'Label Summary', $formatBold );
    $worksheetSum->write( 2, 0, 'Label',         $formatBold );
    $worksheetSum->write( 2, 1, [ 'Units', $currencyCode . ' Gross', $currencyCode . ' Net' ], $formatRBold );

    while ( my $line = <INPUT> ) {
        chomp($line);
        my @lineValues = split( /\t/, $line );
        foreach my $item (@lineValues) {
            if ( $item =~ m/^\"(.*)\"$/ ) {
                $item = $1;
                $item =~ s/\"\"/\"/g;
            }
        }

        $curLabel = $lineValues[0];

        # is this the first iteration?
        # OR
        # are we doing a new label's data?
        if ( !ref($worksheet) || ( defined $prevLabel && $prevLabel ne '' && $curLabel ne $prevLabel ) ) {
            push @{ $labelSummary{order} }, $curLabel;
            $labelSummary{$curLabel} = { units => 0, revenue => 0 };

            # excel doesn't like the characters []:*?/\ in tab names, so we
            # convert them to underscores and limit them to 29 characters for good measure
            # (excel limit is 31 characters and we tack on a page number below (sometimes))
            # every character is escaped in the regex below (*hopefully* makes it more clear?)
            $excelLabel = substr( $curLabel, 0, 29 );
            $excelLabel =~ s/[\[\]\:\*\?\/\\]/_/g;

            # okay, so we also have to check for uniqueness within the 29 characters...
            if ( $excelTabs{ lc($excelLabel) } ) {
                for ( my $i = 1 ; $i < 100 ; $i++ ) {
                    substr( $excelLabel, -( length($i) + 1 ) ) = "-" . $i;
                    if ( !$excelTabs{ lc($excelLabel) } ) {
                        last;
                    }
                }
            }
            $excelTabs{ lc($excelLabel) } = 1;

            # do we need to print the totals for this label?
            if ( $labelSum && $labelSum > 0 ) {

                # create a blank data row that we can add the total to
                my @totalLine = @headerValues;
                for ( my $i = 0 ; $i <= $#totalLine ; $i++ ) {
                    $totalLine[$i] = '';
                }
                $totalLine[ $totalIndex - 1 ] = "Total";
                $totalLine[$totalIndex] = Common::RSMath::round( $labelSum, 2 );

                # put an extra line between the data and the total
                $count += 2;
                $worksheet->write( $count, 0, \@totalLine, $formatBold );

                $labelSum = 0;
            }

            # adjust counters
            $count       = 0;
            $labelPageNo = 1;

            # create new worksheet
            $worksheet = $workbook->add_worksheet($excelLabel);

            # start the sheet with the header row
            $worksheet->write( $count++, 0, \@headerValues, $formatBold );
        }

        # have we reached the max row for excel?
        if ( $count >= 65535 ) {

            # adjust our counters
            $count = 0;
            $labelPageNo++;

            # create new worksheet
            $worksheet = $workbook->add_worksheet( $excelLabel . "-" . $labelPageNo );

            # start the sheet with the header row
            $worksheet->write( $count++, 0, \@headerValues, $formatBold );
        }

        # ok then, write out the data to the sheet
        for ( my $i = 0 ; $i <= $#lineValues ; $i++ ) {

            # for numeric fields we want write() to be clever, otherwise write as a string
            # (keeps leading zeros on upcs, doesn't convert date/formula looking text to dates/formulas)
            # (and don't bother if there's nothing to write at all)
            if ( defined( $lineValues[$i] ) && $lineValues[$i] ne '' ) {
                if ( $headerValues[$i] =~ /service-id|disc-no|track-no|unit|currency-conv|price|dist-fee/ ) {
                    $worksheet->write( $count, $i, $lineValues[$i] );
                } else {
                    $worksheet->write_string( $count, $i, $lineValues[$i] );
                }
            }
        }
        $count++;

        # sum up the usd-ext-price column (last column)
        $labelSum                             += $lineValues[$totalIndex];
        $labelSummary{$curLabel}{units}       += $lineValues[$unitsIndex];
        $labelSummary{$curLabel}{revenue}     += $lineValues[$totalIndex];
        $labelSummary{$curLabel}{net_revenue} += $lineValues[$netRevenueIndex];
        $prevLabel = $curLabel;
    }

    # don't forget the total for the last label
    if ( $labelSum > 0 ) {

        # create a blank data row that we can add the total to
        my @totalLine = @headerValues;
        for ( my $i = 0 ; $i <= $#totalLine ; $i++ ) {
            $totalLine[$i] = '';
        }
        $totalLine[ $totalIndex - 1 ] = "Total";
        $totalLine[$totalIndex] = Common::RSMath::round( $labelSum, 2 );
        $count += 2;
        $worksheet->write( $count, 0, \@totalLine, $formatBold );
    }

    # now go back and populate the summary tab
    my $dollarFormat = $workbook->add_format();

    #$dollarFormat->set_num_format(0x07);
    $dollarFormat->set_num_format( $currencySymbol . "#,##0.00" );
    $dollarFormat->set_bold();
    my $line          = 3;
    my $sumUnits      = 0;
    my $sumRevenue    = 0;
    my $sumNetRevenue = 0;
    map {
        $sumUnits      += $labelSummary{$_}{units};
        $sumRevenue    += $labelSummary{$_}{revenue};
        $sumNetRevenue += $labelSummary{$_}{net_revenue};
        $worksheetSum->write( $line, 0, [ $_, $labelSummary{$_}{units} ] );
        $worksheetSum->write( $line,   2, Common::RSMath::round( $labelSummary{$_}{revenue},     2 ), $dollarFormat );
        $worksheetSum->write( $line++, 3, Common::RSMath::round( $labelSummary{$_}{net_revenue}, 2 ), $dollarFormat );
    } @{ $labelSummary{order} };

    $line++;
    $worksheetSum->write( $line, 0, [ 'Total', $sumUnits ], $formatBold );
    $worksheetSum->write( $line, 2, Common::RSMath::round( $sumRevenue,    2 ), $dollarFormat );
    $worksheetSum->write( $line, 3, Common::RSMath::round( $sumNetRevenue, 2 ), $dollarFormat );

    return $excelFile;
}

sub CreateRoyaltyReportXML {
    my $class     = shift;
    my %args      = @_;
    my $client_id = $args{client_id} || return "no client id passed";

    my $txtFile = $args{src_file} || return undef;    # return usage("missing file parameter");

    return undef if ( !-e $txtFile );

    my $client  = _getClientInfo($client_id);
    my $xmlFile = _getReportName(
        type     => 'royalty_xml',
        period   => $args{period_id},
        client   => $client->{clean_name},
        clientID => $client_id,
    );

    open INPUT,  $txtFile    or return undef;         # die "Couldn't open input file $txtFile: $!";
    open OUTPUT, ">$xmlFile" or return undef;         # die "Couldn't open output file $xmlFile: $!";

    my $headerLine = <INPUT>;
    chomp($headerLine);
    my @fields = split( /\t/, $headerLine );

    print OUTPUT "<royalty>\n";

    while ( my $line = <INPUT> ) {
        chomp($line);
        my @values = split( /\t/, $line );

        if ( $#values == $#fields ) {
            print OUTPUT "\t<sale>\n";
            foreach my $i ( 0 .. $#fields ) {
                print OUTPUT "\t\t<" . $fields[$i] . ">" . $values[$i] . "</" . $fields[$i] . ">\n";
            }
            print OUTPUT "\t</sale>\n";
        }
    }

    print OUTPUT "</royalty>\n";

    close(OUTPUT);
    close(INPUT);

    return $xmlFile;
}

# perl -MSale::Report -e "print Sale::Report->CreateRoyaltyReportConsolidation(period_id => 0, client_id=> 0)"
sub CreateRoyaltyReportConsolidation {
    my $class     = shift;
    my %args      = @_;
    my $period_id = $args{period_id};    # || return undef;
    my $client_id = $args{client_id};
    unless ($client_id) {
        print STDERR "no client id passed\n";
        return undef;
    }
    my $client = _getClientInfo($client_id);

    my $tmp_dir = REPORT_BASE_DIR . '/tmp';
    if ( !-d $tmp_dir && !mkdir($tmp_dir) ) {
        print STDERR "can't create tmp: $tmp_dir\n";
        return undef;
    }

    my $dest_file_path = _getReportName(
        type     => 'royalty_consolidation',
        period   => $period_id,
        client   => $client->{clean_name},
        clientID => $client_id,
    );
    return 1 if ( -f $dest_file_path && -s $dest_file_path );

    print STDERR "output: $dest_file_path\n";

    # let's use a unique name for the tmp file
    my $tmp_file_path = "$tmp_dir/$client->{clean_name}.txt.$$";

    unless ( open TMP, ">$tmp_file_path" ) {
        print STDERR "can't create file $tmp_file_path\n";
        return undef;
    }

    my $createTable = '
		create temporary table consolidation_report(
        product_id int(10) unsigned,
        product_type_id tinyint(3) unsigned,
        catalog_num varchar(255),
        distributor_name varchar(60),
        service_name varchar(60),
        format_type char(1),
        date_end date,
        track_title varchar(255),
        track_isrc varchar(255),
        album_title varchar(255),
        album_id int(10) unsigned,
        artist_id int(10) unsigned,
        label_id int(10) unsigned,
        units int(10),
        sales_total decimal(18,8)
    )';

    my $trackSQL = "INSERT INTO consolidation_report (product_id, product_type_id, distributor_name, service_name,
       format_type, date_end, track_title, track_isrc, album_id, artist_id, units, sales_total)
          (SELECT p.product_id, p.product_type_id, fs.service_name, v.service_name, s.format_type, s.date_end,
             t.title, s.isrc, t.album_id, t.artist_id,
             sum(s.units) as units, sum(s.units * s.price * s.conversion_rate) as sales_total
           FROM file f, sale s, track t, product p, service v, service fs
           WHERE f.period_id=$period_id AND f.file_id=s.file_id AND s.product_id=p.product_id AND
             p.asset_id=t.track_id and s.product_type='T' and v.service_id=ifnull(s.service_id, f.service_id) AND
			 fs.service_id = f.service_id
           GROUP BY v.service_name, s.format_type, s.date_end, p.product_id)";

    my $trackUpdateSQL = "UPDATE consolidation_report c, album a
        SET c.catalog_num=a.catalog_number, c.album_title=a.title, c.label_id=a.label_id
        WHERE c.album_id=a.album_id AND c.product_type_id=4";

    my $albumSQL = "INSERT INTO consolidation_report (product_id, product_type_id, catalog_num, distributor_name, service_name,
       format_type, date_end, album_title, artist_id, label_id, units, sales_total)
          (SELECT p.product_id, p.product_type_id, a.catalog_number, fs.service_name, v.service_name, s.format_type, s.date_end,
             a.title, a.artist_id, a.label_id,
             sum(s.units) as units, sum(s.units * s.price * s.conversion_rate) as sales_total
           FROM file f, sale s, album a, product p, service v, service fs
           WHERE f.period_id=$period_id AND f.file_id=s.file_id AND s.product_id=p.product_id AND
             p.asset_id=a.album_id and s.product_type='A' and v.service_id=ifnull(s.service_id, f.service_id) AND
			 fs.service_id = f.service_id
           GROUP BY v.service_name, s.format_type, s.date_end, p.product_id)";

    my @output_fields =
      qw(catalog_num period_name format_type date_end service_name distributor_name track_title track_isrc album_title artist_name label_name units sales_total);

    my $reportSQL = "SELECT c.catalog_num, c.distributor_name, c.service_name, c.format_type, c.date_end,
        c.track_title, c.track_isrc, c.album_title, a.name as artist_name, l.label_name,
        sum(c.units) as units, sum(c.sales_total) as sales_total
        FROM consolidation_report c, artist a, label l
        WHERE c.artist_id=a.artist_id AND c.label_id=l.label_id
        GROUP BY c.service_name, c.format_type, c.date_end, c.product_id
        ORDER BY c.service_name, c.format_type, c.date_end, c.album_title";

    my $rsdb = Common::RSDB->new( client_id => $client_id );
    $rsdb->DoCmd($createTable);
    $rsdb->DoCmd($trackSQL);
    $rsdb->DoCmd($trackUpdateSQL);
    $rsdb->DoCmd($albumSQL);

    my $sth = $rsdb->DoCmd($reportSQL);

    my $period = Period::RoyaltyPeriod->new( client_id => $client_id, period_id => $period_id );
    my $period_name = $period->Name() || 'N/A';

    print TMP join(
        "\t",
        (
            'catalog-id',
            'period name',
            'format',
            'period end',
            'service name',
            'distributor name',
            'track name',
            'track_isrc',
            'album name',
            'album artist',
            'label name',
            'units',
            'sales total'
        )
      ),
      "\n";

    while ( my $row = $sth->fetchrow_hashref() ) {
        $row->{period_name} = $period_name;

        #$row->{sales_total} = sprintf("%0.8f", $row->{sales_total});
        $row->{units}       = _formatNumber( $row->{units} );
        $row->{sales_total} = _formatNumber( $row->{sales_total} );
        print TMP join( "\t", @$row{@output_fields} ), "\n";
    }
    close(TMP);

    $rsdb->DoCmd('drop table consolidation_report');

    rename( $tmp_file_path, $dest_file_path ) || die "CreateRoyaltyConsolidationReport: can't copy file";

    return 1;
}

sub CreateReconciliationReport {
    my $class     = shift;
    my %args      = @_;
    my $period_id = $args{period_id};                                   # || return undef;
    my $client_id = $args{client_id} || return "no client id passed";
    my $atomic    = $args{atomic} || 0;

    my $client = _getClientInfo($client_id);

    my $locale           = Common::Client::Current()->Locale();
    my $baseCurrencyCode = $locale->currencyFormat()->currencyCode();

    my $tmp_dir = REPORT_BASE_DIR . '/tmp';
    if ( !-d $tmp_dir && !mkdir($tmp_dir) ) {
        print STDERR "can't create tmp: $tmp_dir\n";
        return undef;
    }

    my $dest_file_path = _getReportName(
        type     => 'reconciliation',
        period   => $period_id,
        client   => $client->{clean_name},
        clientID => $client_id,
    );
    return 1 if ( -f $dest_file_path && -s $dest_file_path );

    my $tmp_file_path = "$tmp_dir/$client->{clean_name}.xls.$$";

    my %period_cache = ();
    my $period = Period::RoyaltyPeriod->new( client_id => $client_id );
    $period->Load( period_id => $period_id );
    $period_cache{$period_id} = $period->Name || sprintf( "%s_to_%s", $period->StartDate, $period->EndDate );

    my $services = Client::Service::GetAllServiceNames( client_id => $client_id );

    my $files = RPS::File::Files->new( client_id => $client_id );
    $files->GetParentsByPeriod( period_id => $period_id, filter_type => 'atomic', filter_value => $atomic );

    my @reconcile = ();
    my $sales = RPS::File::Sales->new( client_id => $client_id );

    while ( ( my $fileObj = $files->GetNext() ) ) {
        next unless ( $fileObj->FileStatus == File::File::STATUS_CLOSED or $fileObj->FileStatus == File::File::STATUS_OPEN );

        my $file = _getFamilyHistoryRelativeToPeriod( $fileObj, $period_id, $baseCurrencyCode );

        next unless $file;
        next if ( $period_id == 0 and !$file->{linesCurrent} and !$file->{linesPost} );

        if ( $fileObj->FileType == File::File::FILETYPE_LICENSE_INCOME ) {
            $file->{pd}          = '';                 # flag as physical or digital
            $file->{serviceName} = 'License Income';
            $file->{fileID}      = $fileObj->FileID;
        } else {
            $file->{pd} = $fileObj->Physical ? 'P' : 'D';                # flag as physical or digital
            $file->{serviceName} = $services->{ $fileObj->ServiceID };
            $file->{fileID} = $file->{grand_child_id} || $file->{member_id_list}->[-1] || $fileObj->FileID;
        }

        $file->{fileName} = $fileObj->OrigFileName;

        #$file->{fileID} = $file->{grand_child_id} || $file->{member_id_list}->[-1] || $fileObj->FileID;

        my $orig_period = $fileObj->PeriodID;
        unless ( $period_cache{$orig_period} ) {
            $period->Load( period_id => $orig_period );
            $period_cache{$orig_period} = $period->Name || sprintf( "%s_to_%s", $period->StartDate, $period->EndDate );
        }
        $file->{origPeriodName} = $period_cache{$orig_period};

        ( $file->{dateBegin}, $file->{dateEnd} ) = $sales->GetDateRangeByFileID( $file->{member_id_list} );
        if ( $period_id == 0 && $file->{linesPost} ) {
            my $physicalFlag = $fileObj->Physical;

            # We want to treat license income the same as physical revenue in this case.
            #
            if ( $fileObj->FileType == File::File::FILETYPE_LICENSE_INCOME ) {
                $physicalFlag = 1;
            }

            my $rev_summary = $sales->GetRevenueSummaryByFileID( $file->{fileID}, $client->{skip_free}, $physicalFlag, $fileObj->FileType );
            $file->{revCurrent}   += $rev_summary->{matched}{revenue}   || 0;
            $file->{revDontMatch} += $rev_summary->{dontmatch}{revenue} || 0;
            $file->{revPost} = $rev_summary->{unmatched}{revenue} || 0;
            $file->{unitsCurrent}   += $rev_summary->{matched}{units}   || 0;
            $file->{unitsDontMatch} += $rev_summary->{dontmatch}{units} || 0;
            $file->{unitsPost} = $rev_summary->{unmatched}{units} || 0;
            $file->{linesCurrent}   += $rev_summary->{matched}{lines}   || 0;
            $file->{linesDontMatch} += $rev_summary->{dontmatch}{lines} || 0;
            $file->{linesPost} = $rev_summary->{unmatched}{lines} || 0;
            $file->{freeCurrent} += $rev_summary->{free}{units} || 0;
            $file->{revTotal} = $file->{revPrevious} + $file->{revCurrent} + $file->{revDontMatch} + $file->{revPost};
        }

        push( @reconcile, $file );
    }

    # now dump @reconcile to excel
    my $local_currency_format = Common::Client::Current()->Locale()->formatMoney(0.00);
    my $workbook              = Spreadsheet::WriteExcel->new($tmp_file_path);
    die "ERROR!!! Spreadsheet::WriteExcel failed to return a workbook for path $tmp_file_path" unless $workbook;

    my $worksheet        = $workbook->add_worksheet();
    my $fmt_bold         = $workbook->add_format( bold => 1 );
    my $fmt_header       = $workbook->add_format( bold => 1, text_wrap => 1 );
    my $fmt_revenue      = $workbook->add_format( align => 'right', num_format => $local_currency_format );
    my $fmt_summary      = $workbook->add_format( align => 'right', bold => 1, num_format => $local_currency_format );
    my $fmt_number       = $workbook->add_format( align => 'right', num_format => '0' );
    my $fmt_revenue_bold = $workbook->add_format( align => 'right', bold => 1, num_format => $local_currency_format );
    my $fmt_number_bold  = $workbook->add_format( align => 'right', bold => 1, num_format => '0' );

    my ( $col_map, $col_base, $col_rev_sum, $col_num_sum );
    if ( $client_id == WMG ) {
        ( $col_map, $col_base, $col_rev_sum, $col_num_sum ) = _getReconcileFieldsWMG();
    } else {
        ( $col_map, $col_base, $col_rev_sum, $col_num_sum ) = _getReconcileFields();
    }
    my @all_columns = map { $col_map->{$_} } @$col_base, @$col_rev_sum, @$col_num_sum;

    my $row = 0;
    $worksheet->write( $row++, 0, $client->{name},             $fmt_bold );
    $worksheet->write( $row++, 0, 'ACCOUNTING RECONCILIATION', $fmt_bold );
    $worksheet->write( $row++, 0, $period_cache{$period_id},   $fmt_bold );
    $worksheet->write( $row++, 0, '' );
    $worksheet->write( $row++, 0, \@all_columns,               $fmt_header );

    my %summary = ();

    foreach my $href (@reconcile) {
        $href->{fileName} =~ s/-[a-z]\././;
        map { $summary{$_} += $href->{$_} } @$col_rev_sum, @$col_num_sum;

        my $col = 0;
        map { $worksheet->write( $row, $col++, $href->{$_} ), $fmt_number } @$col_base;

        map { $worksheet->write_number( $row, $col++, $href->{$_}, $fmt_revenue ) } @$col_rev_sum;

        map { $worksheet->write_number( $row, $col++, _formatNumber( $href->{$_} ), $fmt_number ) } @$col_num_sum;

        $row++;
    }
    my $col = scalar @$col_base;
    $worksheet->write( $row, 0, 'Totals', $fmt_bold );
    map { $worksheet->write_number( $row, $col++, $summary{$_}, $fmt_revenue_bold ) } @$col_rev_sum;
    map { $worksheet->write_number( $row, $col++, _formatNumber( $summary{$_} ), $fmt_number_bold ) } @$col_num_sum;

    # JPK - Had to add this explicit call to close() after we moved the report files to the shared drive.
    # For reasons I don't fully understand, letting the file implicitly close (by having $workbook go out of scope)
    # AFTER we rename the temp file was causing the file to be deleted.
    # Lesson here:  Close your files before renaming them...
    #
    $workbook->close() or die "Error closing workbook!: $!";

    rename( $tmp_file_path, $dest_file_path ) || die "CreateReconciliationReport: can't copy file";

    return 1;
}

sub CreateBillingReport {
    my $class    = shift;
    my %args     = @_;
    my $periodID = $args{period_id};                                   # || return undef;
    my $clientID = $args{client_id} || return "no client id passed";
    my $atomic   = $args{atomic} || 0;

    my $client = _getClientInfo($clientID);

    my $locale           = Common::Client::Current()->Locale();
    my $baseCurrencyCode = $locale->currencyFormat()->currencyCode();

    my $tmp_dir = REPORT_BASE_DIR . '/tmp';
    if ( !-d $tmp_dir && !mkdir($tmp_dir) ) {
        print STDERR "can't create tmp: $tmp_dir\n";
        return undef;
    }

    my $dest_file_path = _getReportName(
        type     => 'billing',
        period   => $periodID,
        client   => $client->{clean_name},
        clientID => $clientID,
    );
    return 1 if ( -f $dest_file_path && -s $dest_file_path );

    my $tmp_file_path = "$tmp_dir/$client->{clean_name}.txt.$$";

    my $services = Client::Service::GetAllServiceNames( client_id => $clientID );

    my $files = Raptor::DB::Item::File->GetByPeriodID($periodID);

    my %digitalSales;
    my %physicalSales;
    my %licenseIncome;

    while ( my $file = $files->next() ) {
        my $serviceID   = $file->service_id;
        my $serviceName = $services->{$serviceID};
        my $fileID      = $file->file_id;
        my $fileObj     = RPS::File::File->new( file_id => $fileID );

        my $revenue;
        if ( $file->input_conversion_rate ) {
            $revenue = $fileObj->AdjustedRevenue;
        } else {
            $revenue = $file->revenue;
        }

        if ( $file->type_id eq File::File::FILETYPE_LICENSE_INCOME ) {

            # license income (no service name)
            $licenseIncome{revenue} += $revenue;
        } elsif ( $file->physical == 1 ) {

            # physical sales
            $physicalSales{$serviceName}{revenue} += $revenue;
        } elsif ( $file->physical == 2 ) {

            # digital and/or physical
            my $sales = Raptor::DB::Item::Sale->GetByFileID( $file->file_id );
            while ( my $sale = $sales->next() ) {
                my $distFee = $fileObj->GetDistFeePct( $sale->format_type );
                if ($distFee) {
                    $distFee = ( 100 - $distFee ) * .01;
                } else {
                    $distFee = 1;
                }
                if (   $sale->product_type eq RPS::File::Sale::TYPE_TRACK
                    || $sale->product_type eq RPS::File::Sale::TYPE_ALBUM ) {
                    $digitalSales{$serviceName}{revenue} += ( $sale->price * $sale->units * $sale->conversion_rate * $distFee );
                } else {
                    $physicalSales{$serviceName}{revenue} += ( $sale->total_revenue * $sale->conversion_rate * $distFee );
                }
            }
        } else {

            # digital sales
            $digitalSales{$serviceName}{revenue} += $revenue;
        }
    }

    unless ( open TMP, ">$tmp_file_path" ) {
        print STDERR "can't create file $tmp_file_path\n";
        return undef;
    }

    my @headers = ( "Customer", "Type", "Service", "RS Customer ID", "Revenue", "Billable" );
    print TMP join( "\t", @headers ), "\r\n";

    # !!! Need to separate License Income

    foreach my $serviceName ( sort { uc($a) cmp uc($b) } keys %digitalSales ) {
        my $revenue  = sprintf( "%.2f", $digitalSales{$serviceName}{revenue} );
        my $billable = sprintf( "%.2f", 0 );
        if ( $revenue > 0 ) {
            $billable = $revenue;
        }
        my @rowData = ( $client->{name}, "Digital", $serviceName, $clientID, $revenue, $billable );
        print TMP join( "\t", @rowData ), "\r\n";
    }

    foreach my $serviceName ( sort { uc($a) cmp uc($b) } keys %physicalSales ) {
        my $revenue  = sprintf( "%.2f", $physicalSales{$serviceName}{revenue} );
        my $billable = sprintf( "%.2f", 0 );
        if ( $revenue > 0 ) {
            $billable = $revenue;
        }
        my @rowData = ( $client->{name}, "Physical", $serviceName, $clientID, $revenue, $billable );
        print TMP join( "\t", @rowData ), "\r\n";
    }

    if ( $licenseIncome{revenue} ) {
        my $revenue  = sprintf( "%.2f", $licenseIncome{revenue} );
        my $billable = sprintf( "%.2f", 0 );
        if ( $revenue > 0 ) {
            $billable = $revenue;
        }
        my @rowData = ( $client->{name}, "License Income", '', $clientID, $revenue, $billable );
        print TMP join( "\t", @rowData ), "\r\n";
    }

    close TMP;

    rename( $tmp_file_path, $dest_file_path ) || die "CreateBillingReport: can't copy file";

    return 1;
}

sub GetRoyaltyReportFile {
    my $class     = shift;
    my %args      = @_;
    my $period    = $args{period_id};                   # || return undef;
    my $client_id = $args{client_id} || return undef;
    my $file_type = $args{file_type} || 'royalty';

    my $client    = _getClientInfo($client_id);
    my $file_name = _getReportName(
        type     => $file_type,
        period   => $period,
        client   => $client->{clean_name},
        clientID => $client_id,
    ) || return undef;

    return wantarray ? ( $file_name, $client->{clean_name} ) : $file_name;
}

sub CreateTrackerExcelFile {
    my $self          = shift;
    my $client_id     = shift;
    my $include_notes = shift;

    return undef unless ($client_id);

    my $client = _getClientInfo($client_id);

    my $fileName = _getReportName(
        type     => 'trackerpage',
        period   => 0,
        client   => $client->{clean_name},
        clientID => $client_id,
    );

    my $locale           = Common::Client::Current()->Locale();
    my $baseCurrencyCode = $locale->currencyFormat()->currencyCode();

    my $local_currency_format = $locale->formatMoney(0.00);

    my $workbook = Spreadsheet::WriteExcel->new( $fileName . ".$$" );

    my $worksheet = $workbook->add_worksheet('Current Period');

    my $fmt_header  = $workbook->add_format( bold  => 1 );
    my $fmt_right   = $workbook->add_format( align => 'right' );
    my $fmt_revenue = $workbook->add_format( align => 'right', num_format => $local_currency_format );
    my $fmt_foreign = $workbook->add_format( align => 'right', num_format => '0.00' );
    my $fmt_number  = $workbook->add_format( align => 'right', num_format => '0' );

    #my $fmt_date    = $workbook->add_format(align => 'right', num_format => 'm/d/yyyy');

    my $db = Common::RSDB->new( client_id => $client_id );
    my $dbh = $db->DBH;

    my $sql = qq/
        SELECT
            service.service_name                         AS service,
            file.orig_file_name                          AS file,
            DATE_FORMAT( file.date_created, '%Y-%m-%d' ) AS received,
            file.units                                   AS units,
            IF( file_status = 4, 'Open', 'Finished' )    AS status,
            file.revenue                                 AS revenue,
            file.currency_code                           AS c_code,
            SUM( cr.revenue * cr.conversion_rate )       AS c_revenue,
            file.notes                                   AS notes,
            file.records                                 AS records,
            file.total_exceptions                        AS exc_init,
            file.remaining_exceptions                    AS exc_left
        FROM service, file
        LEFT JOIN (
            SELECT file_id, revenue, conversion_rate
            FROM user_input_conversion_rate
            WHERE file_id NOT IN (
                SELECT DISTINCT file_id
                FROM user_input_conversion_rate
                WHERE conversion_rate = 0
            )
        ) AS cr ON file.file_id = cr.file_id
        WHERE 1
            AND service.service_id = file.service_id
            AND period_id = 0
            AND file_status IN (4,5)
            AND parent_file_id IS NULL
        GROUP BY file.file_id
        ORDER BY file.file_status, service.service_name, file.orig_file_name
    /;

    my $sth  = $dbh->prepare($sql);
    my $rows = $sth->execute();

    my $i = 0;
    $worksheet->set_column( $i, $i, 16 );
    $worksheet->write( 0, $i++, 'service', $fmt_header );
    $worksheet->set_column( $i, $i, 36 );
    $worksheet->write( 0, $i++, 'file', $fmt_header );
    $worksheet->set_column( $i, $i, 8 ) if ($include_notes);
    $worksheet->write( 0, $i++, 'notes', $fmt_header ) if ($include_notes);
    $worksheet->set_column( $i, $i, 10 );
    $worksheet->write( 0, $i++, 'received', $fmt_header );
    $worksheet->set_column( $i, $i, 16 );
    $worksheet->write( 0, $i++, 'units', $fmt_header );
    $worksheet->set_column( $i, $i, 16 );
    $worksheet->write( 0, $i++, 'revenue', $fmt_header );
    $worksheet->set_column( $i, $i, 16 );
    $worksheet->write( 0, $i++, 'foreign_revenue', $fmt_header );
    $worksheet->set_column( $i, $i, 16 );
    $worksheet->write( 0, $i++, 'foreign_currency', $fmt_header );
    $worksheet->set_column( $i, $i, 8 );
    $worksheet->write( 0, $i++, 'records', $fmt_header );
    $worksheet->set_column( $i, $i, 16 );
    $worksheet->write( 0, $i++, 'initial exceptions', $fmt_header );
    $worksheet->set_column( $i, $i, 20 );
    $worksheet->write( 0, $i++, 'remaining exceptions', $fmt_header );
    $worksheet->set_column( $i, $i, 8 );
    $worksheet->write( 0, $i++, 'status', $fmt_header );

    my $row = 1;
    while ( my $data = $sth->fetchrow_hashref() ) {
        $i = 0;
        $worksheet->write( $row, $i++, $data->{service} );
        $worksheet->write( $row, $i++, $data->{file} );
        $worksheet->write_string( $row, $i++, $data->{notes} ) if ($include_notes);
        my $dateReceived = _formatDate( $data->{received} );
        $worksheet->write( $row, $i++, $dateReceived );
        $worksheet->write_number( $row, $i++, $data->{units}, $fmt_number );
        if ( $data->{c_code} ne Common::Client::Current()->Locale()->currencyFormat()->currencyCode() ) {

            if ( $data->{c_revenue} ne "" ) {
                $worksheet->write( $row, $i++, _formatMoney( $data->{c_revenue} ), $fmt_right );
            } else {
                $worksheet->write( $row, $i++, _formatMoney(0), $fmt_right );
            }
            $worksheet->write( $row, $i++, _formatMoney( $data->{revenue}, $data->{c_code} ), $fmt_revenue );
            $worksheet->write( $row, $i++, $data->{c_code} );
        } else {
            $worksheet->write( $row, $i++, _formatMoney( $data->{revenue} ), $fmt_right );
            $i += 2;
        }
        $worksheet->write_number( $row, $i++, $data->{records},  $fmt_number );
        $worksheet->write_number( $row, $i++, $data->{exc_init}, $fmt_number );
        $worksheet->write_number( $row, $i++, $data->{exc_left}, $fmt_number );
        $worksheet->write( $row, $i++, $data->{status} );
        $row++;
    }

    ## save to correct location
    system("mv ${fileName}.$$ $fileName");

    return 1;
}

sub GetTrackerExcelFile {
    my $self      = shift;
    my $client_id = shift;
    return undef unless ($client_id);

    my $client    = _getClientInfo($client_id);
    my $file_name = _getReportName(
        type     => 'trackerpage',
        period   => 0,
        client   => $client->{clean_name},
        clientID => $client_id,
    ) || return undef;
    return wantarray ? ( $file_name, $client->{clean_name} ) : $file_name;
}

# -----------------------------------
# CUSTOM REPORTS
# -----------------------------------

sub CreateRoyaltyReportKOCHRecords {
    my $class   = shift;
    my %args    = @_;
    my $txtFile = $args{src_file} || return undef;
    return undef if ( !-e $txtFile );

    my $client  = _getClientInfo( $args{client_id} );
    my $csvFile = _getReportName(
        type     => 'accounting',
        period   => $args{period_id},
        client   => $client->{clean_name},
        clientID => $args{client_id},
    );
    return $csvFile if ( -f $csvFile && -s $csvFile );

    open INPUT, $txtFile or return undef;    # die "Couldn't open input file $txtFile: $!";
    my $headerLine = <INPUT>;
    chomp($headerLine);
    my @fields = split( /\t/, $headerLine );

    my %customMap = (
        'project_no' => 'album-id',
        'label_id'   => 'album-custom-3',
        'vendor_id'  => 'album-custom-2',
    );

    my @customFields = qw(project_no
      label_id
      vendor_id
      earnings_base
    );

    my %album_data = ();
    while ( my $line = <INPUT> ) {
        chomp($line);
        my @values = split( /\t/, $line );
        my $href = {};

        if ( $#values == $#fields ) {
            foreach my $i ( 0 .. $#fields ) {
                $href->{ $fields[$i] } = $values[$i];
            }

            # convert project_no from AUD-CD-8139 to AUD-8139
            my $project_no = $href->{'album-id'};
            $project_no =~ s/^(\w+)-(\w+)-(\w+)$/$1-$3/;

            my $album_info = {};
            if ( exists $album_data{$project_no} ) {
                $album_info = $album_data{$project_no};
            } else {
                foreach my $field (@customFields) {
                    if ( exists $customMap{$field} ) {
                        if ( $field eq 'project_no' ) {
                            $album_info->{$field} = $project_no;
                        } else {
                            $album_info->{$field} = $href->{ $customMap{$field} };
                        }
                    }
                }
            }
            $album_info->{'earnings_usd'} += $href->{'usd-ext-price'};

            $album_data{$project_no} = $album_info;
        }
    }
    close(INPUT);

    # write out the csv file
    open OUTPUT, ">$csvFile" or return undef;    # die "Couldn't open output file $csvFile: $!";

    # header line
    #print OUTPUT join(",", @customFields) . "\n";

    # sort album_data
    foreach my $album_data ( sort { $a->{'project_no'} cmp $b->{'project_no'} } values %album_data ) {
        my @values = ();
        foreach (@customFields) {
            if ( $album_data->{$_} =~ /^\d*(\.)?\d*$/ ) {
                if ($1) {
                    push @values, sprintf( "%.2f", $album_data->{$_} );
                } else {
                    push @values, $album_data->{$_};
                }
            } else {
                push @values, Common::Util::csvquote( $album_data->{$_} );
            }
        }

        print OUTPUT join( ",", @values ) . "\n";
    }

    close(OUTPUT);

    return $csvFile;

}

sub _getReportName {
    my %args = @_;
    my $type = lc( $args{type} );

    return undef unless $report_type{$type};

    # slight hack to alter the extension for the WMG royalty report
    my $ext = ( $args{clientID} == WMG && $type eq 'royalty' ) ? $report_type{$type}{ext_WMG} : $report_type{$type}{ext};

    my $dest_dir = join( '/', REPORT_BASE_DIR, $args{client} );
    if ( !-d $dest_dir && !mkdir($dest_dir) ) {
        print STDERR "can't create dir: $dest_dir\n";
        return undef;
    }

    return sprintf( "%s/%d_%s.%s", $dest_dir, $args{period}, $report_type{$type}{name}, $ext );
}

sub _getClientInfo {
    my $client_id = shift;

    unless ($client_id) {
        print STDERR "No client id passed\n";
        return;
    }

    return $client_info{$client_id} if exists $client_info{$client_id};
    my $oClient = new Client::Client( client_id => $client_id );

    $client_info{$client_id} = {
        name        => $oClient->ClientName,
        clean_name  => Common::Util::clean_name( $oClient->ClientName ),
        skip_free   => $oClient->SkipFreeTracks,
        report_free => $oClient->ReportFreeTracks,
    };
}

sub _getReconcileFields {

    # list the column names in the order they should appear in the output
    my @baseCols       = qw(fileID origPeriodName serviceName fileName dateBegin dateEnd);
    my @summaryRevCols = qw(revTotal revPrevious revCurrent revDontMatch revPost);
    my @summaryNumCols = qw(
      unitsTotal unitsPrevious unitsCurrent unitsDontMatch unitsPost
      linesTotal linesPrevious linesCurrent linesDontMatch linesPost
    );

    # this maps the above column names to the sometimes ugly header desc
    my %columnMap = (

        #pd             => 'Physical/Digital',
        fileID         => 'File ID',
        origPeriodName => 'Original Period',
        serviceName    => 'Service Name',
        fileName       => 'File Name',
        dateBegin      => 'Begin Date',
        dateEnd        => 'End Date',

        revTotal     => 'Total Revenue Received',
        revPrevious  => 'Previously Processed Revenue',
        revCurrent   => 'Processed Revenue This Period',
        revDontMatch => 'Processed Permanent Exception Revenue This Period',
        revPost      => 'Unprocessed Revenue This Period',

        unitsTotal     => 'Total Paid Units Received',
        unitsPrevious  => 'Previously Processed Paid Units',
        unitsCurrent   => 'Processed Paid Units This Period',
        unitsDontMatch => 'Processed Paid Permanent Exception Units This Period',
        unitsPost      => 'Unprocessed Paid Units This Period',

        linesTotal     => 'Total Lines Received',
        linesPrevious  => 'Previously Processed Lines',
        linesCurrent   => 'Processed Lines This Period',
        linesDontMatch => 'Processed Permanent Exception Lines This Period',
        linesPost      => 'Unprocessed Lines This Period',
    );

    return ( \%columnMap, \@baseCols, \@summaryRevCols, \@summaryNumCols );
}

sub _getReconcileFieldsWMG {

    # list the column names in the order they should appear in the output
    my @baseCols       = qw(fileID origPeriodName serviceName fileName dateBegin dateEnd);
    my @summaryRevCols = qw(revTotal revPrevious revCurrent revDontMatch revPost);
    my @summaryNumCols = qw(
      unitsTotal unitsPrevious unitsCurrent freeCurrent unitsDontMatch unitsPost
      linesTotal linesPrevious linesCurrent linesDontMatch linesPost
    );

    # this maps the above column names to the sometimes ugly header desc
    my %columnMap = (
        fileID         => 'File ID',
        origPeriodName => 'Original Period',
        serviceName    => 'Service Name',
        fileName       => 'File Name',
        dateBegin      => 'Begin Date',
        dateEnd        => 'End Date',

        revTotal     => 'Total Revenue Received',
        revPrevious  => 'Previously Processed Revenue',
        revCurrent   => 'Processed Revenue This Period',
        revDontMatch => 'Processed Permanent Exception Revenue This Period',
        revPost      => 'Unprocessed Revenue This Period',

        unitsTotal     => 'Total Paid Units Received',
        unitsPrevious  => 'Previously Processed Paid Units',
        unitsCurrent   => 'Processed Paid Units This Period',
        freeCurrent    => 'Processed Free Units This Period',
        unitsDontMatch => 'Processed Paid Permanent Exception Units This Period',
        unitsPost      => 'Unprocessed Paid Units This Period',

        linesTotal     => 'Total Lines Received',
        linesPrevious  => 'Previously Processed Lines',
        linesCurrent   => 'Processed Lines This Period',
        linesDontMatch => 'Processed Permanent Exception Lines This Period',
        linesPost      => 'Unprocessed Lines This Period',
    );

    return ( \%columnMap, \@baseCols, \@summaryRevCols, \@summaryNumCols );
}

sub _getFamilyHistoryRelativeToPeriod {
    my ( $file, $base_period, $baseCurrencyCode ) = @_;
    my %family = (
        member_id_list => [ $file->FileID ],
        revPrevious    => 0,
        revCurrent     => 0,
        revDontMatch   => 0,
        revPost        => 0,
        revTotal       => 0,
        unitsPrevious  => 0,
        unitsCurrent   => 0,
        unitsDontMatch => 0,
        unitsPost      => 0,
        unitsTotal     => 0,
        linesPrevious  => 0,
        linesCurrent   => 0,
        linesDontMatch => 0,
        linesPost      => 0,
        linesTotal     => 0,
        freeCurrent    => 0,
    );

    # JPK - Adding this flag to make sure we don't show files that actually had
    # no bearing at all on the period we're generating the report for.
    # The previous logic just relied on there being some sort of revenue someplace,
    # which was not sufficiently distinct enough.
    #
    my $somethingMatchesBasePeriodFlag = 0;

    my $file_conversion_rate;
    if ( $file->InputConversionRate == 1 ) {
        $file_conversion_rate = $file->GetInputConversionRate;
    } else {
        $file_conversion_rate = 1;
    }

    if ( $file->FileStatus == File::File::STATUS_CLOSED ) {
        if ( $file->PeriodID == $base_period ) {
            $family{revCurrent}   = $file->Revenue * $file_conversion_rate;
            $family{unitsCurrent} = $file->Units;
            $family{linesCurrent} = $file->Records;

            my $dmStats = $file->GetDontMatchStats();
            $family{revDontMatch}   = $dmStats->{revenue} * $file_conversion_rate;
            $family{unitsDontMatch} = $dmStats->{units};
            $family{linesDontMatch} = $dmStats->{lines};

            my $freeStats = $file->GetFreeUnitsStats();
            $family{freeCurrent} = $freeStats->{units};

            $family{revCurrent}   -= $dmStats->{revenue} * $file_conversion_rate;
            $family{unitsCurrent} -= $dmStats->{units};
            $family{linesCurrent} -= $dmStats->{lines};
        } else {
            $family{revPrevious}   = $file->Revenue * $file_conversion_rate;
            $family{unitsPrevious} = $file->Units;
            $family{linesPrevious} = $file->Records;
        }
    } else {
        $family{unitsPost} = $file->Units;
        $family{linesPost} = $file->Records;
        $family{revPost}   = $file->Revenue * $file_conversion_rate;
    }

    $family{DistFee} = $file->GetDistFeePct;

    if (   $file->CurrencyCode ne Common::Client::Current()->Locale()->currencyFormat()->currencyCode()
        && $file->GetConversionRate != 1
        && $file->InputConversionRate == 0 ) {
        $family{revPrevious}  = 0;
        $family{revCurrent}   = 0;
        $family{revDontMatch} = 0;
        $family{revPost}      = 0;
    }

    if ( $file->InputDistFee == 1 ) {
        my $distributionFee = $file->GetDistFeePct;
        $family{revPrevious}  -= $family{revPrevious} *  ( $distributionFee * .01 );
        $family{revCurrent}   -= $family{revCurrent} *   ( $distributionFee * .01 );
        $family{revDontMatch} -= $family{revDontMatch} * ( $distributionFee * .01 );
        $family{revPost}      -= $family{revPost} *      ( $distributionFee * .01 );
    }

    if ( $file->PeriodID == $base_period ) {
        $somethingMatchesBasePeriodFlag = 1;
    }

    my $childObj = $file->GetChildObj;
    while ($childObj) {
        push( @{ $family{member_id_list} }, $childObj->FileID );
        my $conversion_rate;
        if ( $childObj->InputConversionRate == 1 ) {
            $conversion_rate = $childObj->GetInputConversionRate;
        } else {
            $conversion_rate = 1;
        }

        if ( $childObj->PeriodID == $base_period ) {
            $somethingMatchesBasePeriodFlag = 1;
        }

        my $distribution_fee;
        if ( $childObj->InputDistFee == 1 ) {
            $distribution_fee = 1 - ( $childObj->GetDistFeePct * .01 );
        } else {
            $distribution_fee = 1;
        }

        if ( $childObj->FileStatus == File::File::STATUS_CLOSED ) {
            if ( $base_period == 0 ) {
                if ( $childObj->PeriodID == $base_period ) {
                    $family{revCurrent}   += $childObj->Revenue * $conversion_rate * $distribution_fee;
                    $family{unitsCurrent} += $childObj->Units;
                    $family{linesCurrent} += $childObj->Records;

                    my $dmStats = $childObj->GetDontMatchStats();
                    $family{revDontMatch}   = $dmStats->{revenue} * $conversion_rate * $distribution_fee;
                    $family{unitsDontMatch} = $dmStats->{units};
                    $family{linesDontMatch} = $dmStats->{lines};

                    my $freeStats = $childObj->GetFreeUnitsStats();
                    $family{freeCurrent} = $freeStats->{units};

                    $family{revCurrent}   -= $dmStats->{revenue} * $conversion_rate * $distribution_fee;
                    $family{unitsCurrent} -= $dmStats->{units};
                    $family{linesCurrent} -= $dmStats->{lines};
                } else {
                    $family{revPrevious}   += $childObj->Revenue * $conversion_rate * $distribution_fee;
                    $family{unitsPrevious} += $childObj->Units;
                    $family{linesPrevious} += $childObj->Records;
                }
            } else {
                if ( $childObj->PeriodID == $base_period ) {
                    $family{revCurrent}   += $childObj->Revenue * $conversion_rate * $distribution_fee;
                    $family{unitsCurrent} += $childObj->Units;
                    $family{linesCurrent} += $childObj->Records;

                    my $dmStats = $childObj->GetDontMatchStats();
                    $family{revDontMatch}   = $dmStats->{revenue} * $conversion_rate * $distribution_fee;
                    $family{unitsDontMatch} = $dmStats->{units};
                    $family{linesDontMatch} = $dmStats->{lines};

                    my $freeStats = $childObj->GetFreeUnitsStats();
                    $family{freeCurrent} = $freeStats->{units};

                    $family{revCurrent}   -= $dmStats->{revenue} * $conversion_rate * $distribution_fee;
                    $family{unitsCurrent} -= $dmStats->{units};
                    $family{linesCurrent} -= $dmStats->{lines};
                } elsif ( $childObj->PeriodID < $base_period && $childObj->PeriodID > 0 ) {
                    $family{revPrevious}   += $childObj->Revenue * $conversion_rate * $distribution_fee;
                    $family{unitsPrevious} += $childObj->Units;
                    $family{linesPrevious} += $childObj->Records;
                } else    # child is after $base_period
                {
                    $family{revPost}   += $childObj->Revenue * $conversion_rate * $distribution_fee;
                    $family{unitsPost} += $childObj->Units;
                    $family{linesPost} += $childObj->Records;
                }
            }
        } else    # this should only happen once per family
        {
            $family{revPost}   += $childObj->Revenue * $conversion_rate * $distribution_fee;
            $family{unitsPost} += $childObj->Units;
            $family{linesPost} += $childObj->Records;

            # grand_child_id is only used when period_id == 0
            $family{grand_child_id} = $childObj->FileID;
        }

        if (   $childObj->CurrencyCode ne Common::Client::Current()->Locale()->currencyFormat()->currencyCode()
            && $childObj->GetConversionRate != 1
            && $childObj->InputConversionRate == 0 ) {
            $family{revPrevious}  = 0;
            $family{revCurrent}   = 0;
            $family{revDontMatch} = 0;
            $family{revPost}      = 0;
        }
        $childObj = $childObj->GetChildObj;
    }

    if ( $base_period > 0 ) {
        return undef
          unless $somethingMatchesBasePeriodFlag
          && ( $family{revCurrent} || $family{revPost} || $family{revDontMatch} || $family{linesCurrent} );
    }

    $family{revTotal}   = $family{revPrevious} + $family{revCurrent} + $family{revPost} + $family{revDontMatch};
    $family{unitsTotal} = $family{unitsPrevious} + $family{unitsCurrent} + $family{unitsPost} + $family{unitsDontMatch};
    $family{linesTotal} = $family{linesPrevious} + $family{linesCurrent} + $family{linesPost} + $family{linesDontMatch};
    return \%family;
}

sub _reformat_dates {
    my $dates = shift;

    my @d1 = split /-/, $dates->[0];
    my @d2 = split /-/, $dates->[1];

    $dates->[0] = sprintf( "%02d%02d%d", $d1[1], $d1[2], $d1[0] );
    $dates->[1] = sprintf( "%02d%02d%d", $d2[1], $d2[2], $d2[0] );
}

sub _reformat_sale_date {
    $_[0] =~ m/^\d\d(\d\d)-(\d\d)-/;
    my ( $y, $m ) = ( $1, $2 );

    return sprintf( "%s-%02d", Month_to_Text($m), $y );
}

sub _determine_sale_date {
    my ( $y0, $m0, $d0 ) = split /\D/, $_[0];
    my ( $y1, $m1, $d1 ) = split /\D/, $_[1];

    return ( $m1 - $m0 != 0 ) ? ( 'Quarterly', "${m1}01$y1" ) : ( 'Monthly', "${m0}01$y0" );
}

sub _formatDate {
    my ($date) = @_;

    $date = Common::Client::Current()->Locale()->formatDate($date);

    return $date;
}

sub _formatNumber {
    my ($value) = @_;

    $value = Common::Client::Current()->Locale()->formatNumber($value);

    return $value;
}

sub _formatMoney {
    my ( $value, $currencyCode ) = @_;

    # Use our own rounding algorithm.
    #
    my $rVal = Common::RSMath::round( $value, 2 );

    if ( defined $currencyCode ) {
        $rVal = Common::Client::Current()->Locale()->formatMoney( $rVal, $currencyCode );
    } else {
        $rVal = Common::Client::Current()->Locale()->formatMoney($rVal);
    }

    return $rVal;
}

sub _formatPercent {
    my ($value) = @_;

    # Use our own rounding algorithm.
    #
    $value = Common::RSMath::round( $value, 2 );

    my $rVal = sprintf( '%.2f', $value );

    $rVal = Common::Client::Current()->Locale()->formatNumber($rVal) . '%';

    return $rVal;
}

###
1;    # Play nicely.
###