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


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 = shift;
	my %args = @_;
	my $period = $args{period_id}; # || return undef;
	my $client_id = $args{client_id};
    my (@error_products,@error_sales);
    Common::Log::Print("In Report.pm");

    unless ($client_id)
    {
        print STDERR "no client id passed\n";;
        return undef;
    }
	my $client = _getClientInfo($client_id);

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

	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;
    }
	binmode( TMP, ':utf8');

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

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

	my @all_rows = ();

  # add extra fields for Instinct
  if ($client_id == 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',
      );

    my $lineCount = 1;  # we'll count the header we'll write too
	while((my $sale = $sales->GetNext()))
	{
		my $href = $sale->GetObjectXML();

        ## skip phys sales
        next if (($sale->ProductType ne 'A') && ($sale->ProductType ne 'T'));

		# 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 = RPS::File::File->new( 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);
            if(!$product->TrackName && !$product->ArtistName && !$product->ProductType) {
                push(@error_sales,$sale->SaleID);
                push(@error_products,$sale->ProductID);
            }

			# 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 RPS::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);
            if(!$product->ArtistName && !$product->ProductType) {
                push(@error_sales,$sale->SaleID);
                push(@error_products,$sale->ProductID);
            }

			# 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 RPS::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->ProductTitle;
		$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->{LabelName} = "Unknown" if(!$product->LabelName);

        $href->{Price}          = Common::RSMath::round($href->{Price},8);
		$href->{TotalPrice}     = $href->{Units} * $href->{Price};
        $href->{ConversionRate} = Common::RSMath::round($href->{ConversionRate},8);
		$href->{USDPrice}       = Common::RSMath::round(($href->{Price} * $href->{ConversionRate}),8);
		$href->{USDTotalPrice}  = $href->{Units} * $href->{USDPrice};
		$href->{Free}           = $href->{Free} ? 'Y' : 'N';

        my $dist_fee = $file->GetDistFeePct($href->{FormatType});
        if ($dist_fee)
        {
            $href->{DistFeePct} = $dist_fee*.01;
            $href->{USDNetPrice} = $href->{USDTotalPrice} * ((100-$dist_fee)*.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}."\"";
        }
    }

    if ($client_id == 11)
    {
        $href->{USDNetExtPrice} = $href->{USDTotalPrice} - $href->{USDMechanicals};
    }

    # Actually, this file didn't have currency symbols to begin with,
    # so there's no need to add them.
    #
    # now that all of the calculations are done,
    # let's format some of the values.
    #$href->{TotalPrice} = _formatMoney($href->{TotalPrice});
    #$href->{USDPrice} = _formatMoney($href->{USDPrice});
    #$href->{USDTotalPrice} = _formatMoney($href->{USDTotalPrice});
    #$href->{USDNetPrice} = _formatMoney($href->{USDNetPrice});
    #$href->{DistFeePct} = _formatMoney($href->{DistFeePct});

    #if ($client_id == 11 and $sale->FormatType eq File::Sale::FORMAT_DOWNLOAD and $href->{Price} != 0 and $href->{ServiceID} != 5)
    #{
    #	$href->{USDMechanicals} = _formatMoney($href->{USDMechanicals});
    #}

    #if ($client_id == 11)
    #{
		#	$href->{USDNetExtPrice} = _formatMoney($href->{USDNetExtPrice});
		#}

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

        $lineCount++;
	}

	# 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.
	my $unsorted_file_path = $dest_file_path.".unsorted";
	my $sorted_file_path = $dest_file_path.".sorted";
	rename($tmp_file_path, $unsorted_file_path) || die "CreateRoyaltyReport: can't copy file";
	my $command = "nice sort -t '\t' -k1,1 -k3,3 -k5,5 -k6,6 -k8,8 -k9,9 ".$unsorted_file_path." > ".$sorted_file_path;
	system($command);
	open(READ,$sorted_file_path);
	binmode( READ, ':utf8');
	open(WRITE,">".$dest_file_path);
	binmode( WRITE, ':utf8');
	my $header = join("\t", @fields);
	$header =~ s/usd/$currencyCodeLC/g;
	print WRITE "$header\n";
	#print WRITE join("\t",@fields) ."\n";
	while(my $data_line=<READ>) {
		print WRITE $data_line;
	}
	close WRITE;
	close READ;

	# we don't need these anymore.
	unlink $unsorted_file_path, $sorted_file_path;

    #mail out product warning if applicable
    if(@error_products>0) {
        my ($mailer) = new Mail::Mailer qw(sendmail);
        my $recipient = "rps_error\@royaltyshare.com";

        my (%headers) =
        (
            'From'    => 'support@royaltyshare.com',
            'To'      => $recipient,
            'Subject' => $client->{clean_name} . " Period: ".$period . " Royalty Report Generation Error",
        );

        my $body = "The following ".$client->{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";
        }

        $mailer->open(\%headers);
        print $mailer $body;
        $mailer->close;
    }

	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 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 = "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.total_exceptions>file.remaining_exceptions or file.records>file.remaining_exceptions or "
             ."file.records=0) and file.parent_file_id is null, 'Pending', "
             ."if(file.total_exceptions>file.remaining_exceptions, 'Finish', 'Open')) 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 user_input_conversion_rate cr on file.file_id=cr.file_id "
             ."WHERE 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 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;

    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, $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.
###