package RPS::Sale::ReportNew;

use strict;

#use warnings;

use lib '/app/tools/common/lib';
use Common::Util;
use Common::Client;

use lib '/app/tools/data_classes/lib';
use Client::Client;
use Client::Service;
use File::File;
use File::Sale;
use Product::ProductAlbum;
use Product::ProductTrack;
use Period::RoyaltyPeriod;

use lib '/app/tools/rps/lib';
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',
    },
);

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 = new File::Sales( client_id => $client_id );
    $sales->GetFinalSalesByPeriod( period_id => $period, file_id => $file_id );

    my $fileObj = 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::CurrenCurrentt()->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->{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 = new File::Sales( client_id => $client_id );
    my $sale_count = $sales->GetFinalSalesByPeriod( period_id => $period, file_id => $file_id );
    return 0 unless $sale_count;

    my $fileObj = 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     = shift;
    my %args      = @_;
    my $period    = $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',
        period   => $period,
        client   => $client->{clean_name},
        clientID => $client_id,
    );
    return 1 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}.txt.$$";

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

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

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

    push( @fields, 'dist-fee', 'usd-net-price', 'media-type', );

    # print the column heading
    print TMP join( "\t", @fields ) . "\n";

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

        my $file = new File::File(
            file_id   => $sale->FileID,
            client_id => $client_id
        );
        if ( $href->{ServiceID} ) {
            $href->{Distributor} = $services->{ $file->ServiceID };
        } else {
            $href->{Distributor} = '';
            $href->{ServiceID}   = $file->ServiceID;
        }
        $href->{ServiceName}     = $services->{ $href->{ServiceID} };
        $href->{ServiceNameSort} = uc( $href->{ServiceName} );

        # the data from the Sale object only contains some of the
        # info we need for the report (units, price, etc.), the
        # rest comes from the Product objects. Get and set those values here.
        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->{ISRC}            = $product->ISRC;
            $href->{MediaType}       = $product->MediaType;
            $href->{TrackNum}        = $product->TrackNumber;
            $href->{TrackCustom1}    = $product->TrackCustom1;
            $href->{TrackCustom2}    = $product->TrackCustom2;
            $href->{TrackCustom3}    = $product->TrackCustom3;
            $href->{DiscNumber}      = $product->DiscNumber;

            ## mechanical calculations for instinct
            ## SKIP IF eMUSIC
            if ( $client_id == 11 and $sale->FormatType eq File::Sale::FORMAT_DOWNLOAD and $href->{Price} != 0 and $href->{ServiceID} != 5 )
            {
                $href->{USDMechanicals} = RPS::Sale::StatRates::getStatRate( date => $sale->DateEnd, sec => $product->Duration );
                $href->{USDMechanicals} *= $href->{Units};
            }
        } elsif ( $sale->ProductType ne "T" && $sale->ProductType ne "M" ) {
            $product = new Product::ProductAlbum( product_id => $sale->ProductID, client_id => $client_id );

            # set some values
            $href->{AlbumArtistName} = $product->ArtistName;
            $href->{ArtistName}      = "";
            $href->{DiscNumber}      = '';

            ## mechanical calculations for instinct
            ## SKIP IF eMUSIC
            if ( $client_id == 11 and $sale->FormatType eq File::Sale::FORMAT_DOWNLOAD and $href->{Price} != 0 and $href->{ServiceID} != 5 )
            {
                $href->{USDMechanicals} = 0;
                map { $href->{USDMechanicals} += RPS::Sale::StatRates::getStatRate( date => $sale->DateEnd, sec => $_ ) }
                  @{ $product->get_track_durations() };
                $href->{USDMechanicals} *= $href->{Units};
            }
        }

        # for the purpose of sorting
        $href->{ProductType}       = $sale->ProductType;
        $href->{UPC}               = $product->UPC;
        $href->{AlbumName}         = $product->AlbumName;
        $href->{ClientAlbumID}     = $product->ClientAlbumID;
        $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->{TotalPrice}    = $href->{Units} * $href->{Price};
        $href->{USDPrice}      = $href->{Price} * $href->{ConversionRate};
        $href->{USDTotalPrice} = $href->{Units} * $href->{USDPrice};
        $href->{Free}          = $href->{Free} ? 'Y' : 'N';
        if ( $file->GetDistFeePct != 0 ) {
            $href->{DistFeePct} = $file->GetDistFeePct;
            $href->{USDNetPrice} = $href->{USDTotalPrice} * ( ( 100 - $file->GetDistFeePct ) * .01 );
        } else {
            $href->{DistFeePct}  = 0;
            $href->{USDNetPrice} = $href->{USDTotalPrice};
        }

        foreach my $key ( keys %$href ) {
            if ( $href->{$key} =~ m/\"/ ) {
                $href->{$key} =~ s/\"/\"\"/g;
                $href->{$key} = "\"" . $href->{$key} . "\"";
            }
        }

        my @values = ();
        map { push @values, $href->{ $field_map{$_} } } @fields;
        print TMP join( "\t", @values ) . "\n";
    }

    # now that we have all the rows (hrefs) in an array,
    # let's sort it and write the data to our file.

    close TMP;

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

    return 1;
}

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

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

    # 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 'usd-ext-price' );
        $unitsIndex      = $ix if ( lc( $headerValues[$ix] ) eq 'units' );
        $netRevenueIndex = $ix if ( lc( $headerValues[$ix] ) eq 'usd-net-price' );
    }

    # create our excel object and reusable formatter
    # each label will get its own worksheet in the spreadsheet.
    require Spreadsheet::WriteExcel::Big;
    my $workbook   = new Spreadsheet::WriteExcel::Big($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', 'USD Gross', 'USD 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] = $labelSum;

                # 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] = $labelSum;
        $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_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, $labelSummary{$_}{revenue},     $dollarFormat );
        $worksheetSum->write( $line++, 3, $labelSummary{$_}{net_revenue}, $dollarFormat );
    } @{ $labelSummary{order} };

    $line++;
    $worksheetSum->write( $line, 0, [ 'Total', $sumUnits ], $formatBold );
    $worksheetSum->write( $line, 2, $sumRevenue,    $dollarFormat );
    $worksheetSum->write( $line, 3, $sumNetRevenue, $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 table consolidation_report(
        product_id int(10) unsigned,
        product_type_id tinyint(3) unsigned,
        catalog_num varchar(255),
        service_name varchar(60),
        format_type char(1),
        date_end date,
        track_title 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, service_name,
       format_type, date_end, track_title, album_id, artist_id, units, sales_total)
          (SELECT p.product_id, p.product_type_id, v.service_name, s.format_type, s.date_end,
             t.title, 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
           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)
           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, 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, 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
           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)
           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 track_title album_title artist_name label_name units sales_total);

    my $reportSQL = "SELECT c.catalog_num, c.service_name, c.format_type, c.date_end,
        c.track_title, 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',
            'track name',
            '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} );
        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 $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 = new File::Files( client_id => $client_id );
    $files->GetParentsByPeriod( period_id => $period_id, filter_type => 'atomic', filter_value => $atomic );

    my @reconcile = ();
    my $sales = new File::Sales( 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 );
        next unless $file;
        next if ( $period_id == 0 and !$file->{linesCurrent} and !$file->{linesPost} );

        $file->{pd}          = $fileObj->Physical ? 'P' : 'D';       # flag as physical or digital
        $file->{serviceName} = $services->{ $fileObj->ServiceID };
        $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 $rev_summary = $sales->GetRevenueSummaryByFileID( $file->{fileID}, $client->{skip_free}, $fileObj->Physical );
            $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;
        }

        push( @reconcile, $file );
    }

    # now dump @reconcile to excel
    require Spreadsheet::WriteExcel;
    my $workbook    = new Spreadsheet::WriteExcel($tmp_file_path);
    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 => '$0.00' );
    my $fmt_summary = $workbook->add_format( align => 'right', bold => 1, num_format => '$0.00' );

    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->{$_} ) } @$col_base;

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

    rename( $tmp_file_path, $dest_file_path ) || die "CreateReconciliationReport: 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,
    );

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

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

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

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

    my $sql =
        "SELECT service.service_name AS service, file.orig_file_name AS file, "
      . "DATE_FORMAT(file.date_created, '%m/%d/%Y') AS received, "
      . "file.units AS units, file.file_status AS status, "
      . "file.revenue AS revenue, file.currency_code AS c_code, "
      . "file.input_conversion_rate AS c_rate, file.notes AS notes, "
      . "file.records as records, file.total_exceptions as exc_init, "
      . "file.remaining_exceptions as exc_left "
      . "FROM file, service "
      . "WHERE service.service_id = file.service_id "
      . "AND file.period_id = 0 AND file.file_status IN (4,5) "
      . "ORDER BY 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);
        $worksheet->write( $row, $i++, $data->{received} );
        $worksheet->write( $row, $i++, $data->{units} );
        if ( $data->{c_code} ne "USD" ) {
            if ( $data->{c_rate} ne "" ) {
                my $revenue = $data->{revenue} * $data->{c_rate};
                $worksheet->write( $row, $i++, $revenue, $fmt_revenue );
            } else {
                $worksheet->write( $row, $i++, 0, $fmt_revenue );
            }
            $worksheet->write( $row, $i++, $data->{revenue}, $fmt_foreign );
            $worksheet->write( $row, $i++, $data->{c_code} );
        } else {
            $worksheet->write( $row, $i++, $data->{revenue}, $fmt_revenue );
            $i += 2;
        }
        $worksheet->write( $row, $i++, $data->{records} );
        $worksheet->write( $row, $i++, $data->{exc_init} );
        $worksheet->write( $row, $i++, $data->{exc_left} );
        $worksheet->write( $row, $i++, ( $data->{status} == 4 ) ? "Open" : "Closed" );
        $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_usd
    );

    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;

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

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

sub _getReconcileFields {

    # list the column names in the order they should appear in the output
    my @baseCols       = qw(pd 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 ) = @_;
    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,
    );

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

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

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

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

    my $childObj = $file->GetChildObj;
    while ($childObj) {
        push( @{ $family{member_id_list} }, $childObj->FileID );

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

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

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

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

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

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

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

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

        $childObj = $childObj->GetChildObj;
    }

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

    my $conversionRate = $file->GetInputConversionRate;
    if ($conversionRate) {
        $family{revPrevious}  *= $conversionRate;
        $family{revCurrent}   *= $conversionRate;
        $family{revDontMatch} *= $conversionRate;
        $family{revPost}      *= $conversionRate;
    }

    my $distributionFee = $family{DistFee};
    if ($distributionFee) {
        $family{revPrevious}  -= ( $family{revPrevious} *  ( $distributionFee * .01 ) );
        $family{revCurrent}   -= ( $family{revCurrent} *   ( $distributionFee * .01 ) );
        $family{revDontMatch} -= ( $family{revDontMatch} * ( $distributionFee * .01 ) );
        $family{revPost}      -= ( $family{revPost} *      ( $distributionFee * .01 ) );
    }

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

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